How to cause JSON_OBJECT to not transform %String fields into JSON? (Leave escaped)

Question

Question

Andrew Hubbard · Nov 19, 2018

When using the JSON_OBJECT() function in Caché SQL on a %String property that contains JSON syntax, it converts the %String into a JSON object instead of escaping it as a string literal. How can I prevent this? (without ridiculous hacks like "add a space to the beginning of the value" as we don't always know which properties will contain these values and I certainly don't want to have to check for nulls and add/remove a space every single place this value is used in the application)

I don't want these strings automatically marshalled into JSON objects.

For example:

SELECT ID, JSON_OBJECT('ID': ID, 'Name':, LastName || ', ' || FirstName, 'Options': Options) as Item FROM Whatever.Whatever

Should give me:

1, '{"ID":1,"Name":"Smith, John","Options":"{\"Color\":\"Blue\",\"Count\":20}"}'

And instead is giving me the marshalled version:

1, '{"ID":1,"Name":"Smith, John","Options":{"Color":"Blue","Count":20}}'

So that when I then call a %FromJSON() in Caché or a JSON.parse() in javascript I am getting an object on the Options property instead of the string

Thank you,

--Andy

Discussion (12)1

Log in or sign up to continue

Eduard Lebedyuk · Nov 20, 2018

I don't think that behavior can be easily modified. JSON_OBJECT checks first symbol of a value and if it's a { or a [, does the conversion.

You can try %ZEN.Auxiliary.altJSONSQLProvider class to generate JSON from SQL queries.

we don't always know which properties will contain these values

Why? Can a property contain JSON or not JSON? You can also try to parse JSON on a first save and save individual values instead.

0 0

Sean Connelly · Nov 20, 2018

I think Andew was looking for the Options to be escaped like so...

{"ID":1,"Name":"Smith, John","Options":"{\"Color\":\"Blue\",\"Count\":20}"}

Hence mentioning the CONCAT trick that does give the desired results...

select JSON_OBJECT('Name':Name, 'Raw': {fn CONCAT(Raw,' ')} )
from Foo.JSON

But given that he doesn't know if the field contains JSON or not he sounds a bit stuck with this approach.

The only thing I can think of with JSON_OBJECT is to append a space to known JSON before storing it, whitespace is valid and any consumer would ignore it.

0 0

Robert Cemper · Nov 20, 2018

There is no option 'NO_JSON' or similar for strings.
so you have to do it by hand.

Assumption according to your description the basic table looks like this: (except for Name)

select ID, Name, Options from Whatever.Whatever

ID	Name	Options
1	Zucherro,Michelle Q.	{"Color":"Green","Count":4}
2	Paraskiv,Alexandra E.	{"Color":"Purple,""Count":6}
3	Ramsay,Jules T.	{"Color":"White""Count":8}
4	Grabscheid,Julie K.	{"Color":"Orange","Count":2}
5	Edwards,Mark S.	{"Color":"Red","Count":1}

Then this might do the trick:
You manually mask out the critical characters first and mask it in after JSON Processing.
No help by the system just your own fate.

SELECT top 5 ID, REPLACE(REPLACE(REPLACE( JSON_OBJECT('ID':ID,'Name': Name,'Options': $TRANSLATE(Options,'{}"','()^') ) ,'"(','{') ,')"','}') ,'^','"') FROM Whatever.Whatever

1	{"ID":1,"Name":"Zucherro,Michelle Q.","Options":{"Color":"Black","Count":7}}
2	{"ID":2,"Name":"Paraskiv,Alexandra E.","Options":{"Color":"Red","Count":1}}
3	{"ID":3,"Name":"Ramsay,Jules T.","Options":{"Color":"Purple","Count":6}}
4	{"ID":4,"Name":"Grabscheid,Julie K.","Options":{"Color":"Green","Count":4}}
5	{"ID":5,"Name":"Edwards,Mark S.","Options":{"Color":"White,""Count":8}}

Not funny but working

You could as well compose your 'personalized' JSON result in a Classmethod and project it as SqlProcedure

0 0

score 0 · Answer 1 · 2018-11-20T10:34:21-05:00

Where this issue first came up for me is in a SQLTable to JSON to SQLTable export/import process I am creating. So, I do not know the layout of the table ahead of time as this can be any table (that inherits from a class that includes a hook for calling the private %SetId(), for import).

It's simple enough to grab the properties from %Dictionary.CompiledProperty and build a query, and the built-in JSON SQL Provider methods mostly work fine for flat tables but this causes dificulty with lists and arrays.

For "array of" properties without too many elements I can use JSONARRAYAGG to simply include them inline:
"(SELECT JSON_ARRAYAGG(JSON_OBJECT('index':element_key,'value':" _ value _ ")) FROM " _ sqlTable _ "_" _ name _ " WHERE " _ sqlTableShort _ " = a.%ID)"

But this doesn't work when any of the values are %Strings that contain JSON data or even just some brackets like "{Hello}"

For "{Hello}" it just throws a -400 SQL error because it's not valid JSON even though the property has nothing to do with JSON and I'm just trying to include a regular ol' string in my result set.

Note: "{Hello" works fine, but "{Hello}" errors out, it checks first and last characters, not just first (I think they fixed that, based on earlier posts I found)

Also, I'm on 2017.2.2.865.0

score 0 · Answer 2 · 2018-11-20T11:35:02-05:00

The problem is that I don't want the strings to be parsed into JSON objects, but left as a string and just escaped into a valid string value.

And I also don't know if the data contains the ( ) or ^ characters so I can't just use them as temporary place holder transforms.

As for a classmethod/SQLProc, at that point I might as well just build the JSON by hand, which looks like I might have to do.

I don't understand why JSON_OBJECT works this way because if it encounters a value it doesn't understand it errors so it can't be trusted. All the benefits of JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, etc are lost because it's not treating values as values and instead interpreting them. Super frustrating.

score 0 · Answer 3 · 2018-11-20T11:42:35-05:00

Eduard Lebedyuk · Nov 20, 2018

Check RESTForms project - it deals with similar issues, especially part 2 - it's about SQL queries.

0 0

score 0 · Answer 4 · 2018-11-20T11:28:25-05:00

The IS JSON predicate only works against valid JSON (as it should) so strings like "{How are you}" cause IS JSON to be false, since it is not JSON, but JSON_OBJECT() still tries to parse it and errors, so there is no DECODE() or CASE/WHEN solution to this using IS JSON.

score 0 · Answer 5 · 2018-11-20T06:59:45-05:00

Eduard Lebedyuk · Nov 20, 2018

There's also IS_JSON function to determine if the value is JSON.

0 0

score 0 · Answer 6 · 2018-11-20T11:54:54-05:00

Yes, I need the plain old value as a string in the JSON output as you say.

I also don't have control over the saving (for this process at least).

I was thinking of maybe appending $CHAR(0) to the end of every non-null string in the select query itself and the just running a $replace on the string, but there are fields that have been set to empty string from SQL (UPDATE Whatever SET Field = '' WHERE ID = 8) which is then returned by CachéSQL as $CHAR(0).

Maybe append something in the $CHAR(128) to $CHAR(159) range since we're running 8 bit Caché and the modified version of 8859-1 that Caché uses doesn't seem to use those characters?

I can build these structures manually but JSON_OBJECT promised such simplicity and it's annoying to find out it interprets the values instead of just passing them through. (And breaking on "{I'm not JSON despite the curly braces}" type strings)

score 0 · Answer 7 · 2018-11-20T11:58:18-05:00

There's still the option to place an enhancement request @WRC.
If granted, version 2019.2 more likely 2020.* or after may include it.
Until then you depend on own writing.

score 0 · Answer 8 · 2018-11-20T15:05:18-05:00

Thanks, I'm going to have to do that I guess.

It should also be noted that nowhere in the documentation for JSON_OBJECT does it mention this behavior.

In fact, it says: "JSON_OBJECT returns object values as either a string (enclosed in double quotes), or a number. Numbers are returned in canonical format. A numeric string is returned as a literal, enclosed in double quotes. All other data types (for example, Date or $List) are returned as a string"

Which also isn't true for another reason as it returns booleans as true/false

score 0 · Answer 9 · 2018-11-28T09:14:15-05:00

If anyone is curious, I did manage to get around this with an ugly workaround:

Set value = "DECODE(" _ value _ ", CHAR(0), CHAR(0), CHAR(128)||" _ value _ ")"

And then replacing the CHAR(128) later:

Set item = results.Get("item")
Set item = $REPLACE(item, $CHAR(128), "")

Note: We are running 8-bit Caché, not Unicode, so $CHAR(128) is unused and available for us as a placeholder