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

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

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.

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.

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

Thanks! I couldn't find the issue, but it was good to try some things and see that Adobe would run from the command line.