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

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

  • 0
  • 0
  • 70
  • 10
  • 2

Answers

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.

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

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 

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.

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

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.

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)

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.

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.  

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

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