JSON Support in IRIS SQL

The IS JSON predicate determines if a data value is in JSON format.

IS JSON (with or without the optional VALUE keyword) returns true for any JSON array or JSON object. This includes an empty JSON array '[]' or an empty JSON object '{}'.

The VALUE keyword and the SCALAR keyword are synonyms.

scalar-expression

A scalar expression that is being checked for JSON formatting.

keyword

An optional argument. One of the following: VALUE, SCALAR, ARRAY, or OBJECT. The default is VALUE.

Let's try it out

USER>do ##class(%SQL.Statement).%ExecDirect(, "SELECT 'yes' is_json_object WHERE ? IS JSON OBJECT", {}).%Display()
is_json_object
yes

1 Rows(s) Affected
USER>do ##class(%SQL.Statement).%ExecDirect(, "SELECT 'yes' is_json_array WHERE ? IS JSON ARRAY", []).%Display()
is_json_array
yes

1 Rows(s) Affected

Seems good, but this is totally ObjectScript way. Let's try with JDBC

• Now there is no more json object or json array, only value or scalar
• Null value not even a json, and not a not json as well, it's just something in between
• JSON_OBJECT, JSON_ARRAY from above, not an object and not an array anyway, it's something else
• JSON is JSON but not an object not an array, so what it is
• There is no way to get is json object/array working via ODBC/JDBC

JSON_TABLE

JSON_TABLE( json-value, json-path col-mapping )

The JSON_TABLE function returns a table that can be used in a SQL query by mapping JSONOpens in a new tab values into columns. Mappings from a JSON value to a column are written as SQL/JSON path language expressions.

As a table-valued function, JSON_TABLE returns a table that can be used in the FROM clause of a SELECT statement to access data stored in a JSON value; this table does not persist across queries. Multiple calls to JSON_TABLE can be made within a single FROM clause and can appear alongside other table-valued functions.

• empty string or null values, are the same
• supported even nested use of json_array/json_object

JSONPath

A SQL/JSON path language expression is a sequence of operators that define a path through a JSON document to a desired set of values.  

In addition to JSON_TABLE, it can be directly used on JSON using ObjectScript using apply method on any json object

Extract values from JSON Array

USER>zwrite [{"value":"test"},{"value":123},{"value":1.23},{"value":""},{"value":null}].apply("$.value")
["test",123,1.23,"",""]   ; <DYNAMIC ARRAY,refs=1>
USER>zwrite [{"value":null}].apply("$.value")
[""]   ; <DYNAMIC ARRAY,refs=1>
USER>zwrite [].apply("$.value")
[]   ; <DYNAMIC ARRAY,refs=1>

And from one JSON Object

USER>zwrite {"value":null}.apply("$.value")
[""]   ; <DYNAMIC ARRAY,refs=1>
USER>zwrite {"value":123}.apply("$.value")
[123]   ; <DYNAMIC ARRAY,refs=1>
USER>zwrite {}.apply("$.value")
[]   ; <DYNAMIC ARRAY,refs=1>
USER>zwrite {"value":""}.apply("$.value")
[""]   ; <DYNAMIC ARRAY,refs=1>

• Again there is no difference between empty string and null
• Even for a single object it still returns an array

Conclusion

Every function tested has problems distinguishing between null and empty strings, which have a difference in SQL or JSON. No way to generate boolean values in JSON.

Working with JSON from Python, would require even more out of this, such as more value types, but it will be so 

At this point, I don't see any purpose for any of these functions. And not sure if it can be used in Python libraries.