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
Comments
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
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.
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.
Check RESTForms project - it deals with similar issues, especially part 2 - it's about SQL queries.
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.
There's also IS_JSON function to determine if the value 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)
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
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
|
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
|
Not funny but working
You could as well compose your 'personalized' JSON result in a Classmethod and project it as SqlProcedure