A conversion function that returns data as a JSON object.
Description
JSON_OBJECT takes a comma-separated list of key:value pairs
(for example, 'mykey':colname) and returns a JSON
object containing those values. You can specify any single-quoted
string as a key name; JSON_OBJECT does not enforce
any naming conventions or uniqueness check for key names. You can
specify for value a column name or other expression.
JSON_OBJECT can be combined in a SELECT statement with other types of select-items. JSON_OBJECT can be specified in other locations where
an SQL function can be used, such as in a WHERE clause.
A returned JSON object has the following format:
{ "key1" : "value1" , "key2" : "value2" , "key3" : "value3"
}
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, with the current %SelectMode determining the format
of the returned value. JSON_OBJECT returns both
key and value values in DISPLAY or ODBC mode if that is the select
mode for the query.
JSON_OBJECT does not support asterisk (*)
syntax as a way to specify all fields in a table.
The returned JSON object column is labeled as an Expression
(by default); you can specify a column alias for a JSON_OBJECT.
Select Mode and Collation
The current %SelectMode property determines the format of the returned JSON object values.
By changing the Select Mode, all Date and %List values are included
in the JSON object as strings with that Select Mode format. You can
override the current Select Mode by applying a format-transformation
function (%EXTERNAL, %INTERNAL, %ODBCIN, %ODBCOUT) to individual field
names within JSON_OBJECT. Applying a format-transformation
function to a JSON_OBJECT has no effect, because
the key:value pairs of a JSON object are strings.
The default collation determines the collation of the returned
JSON object values. You can apply a collation function to a JSON_OBJECT,
converting both keys and values. Generally, you should not apply a
collation function to JSON_OBJECT because keys
are case-sensitive. InterSystems IRIS applies the collation after
JSON object formatting. Therefore, %SQLUPPER(JSON_OBJECT('k1':f1,'k2':f2)) converts all the JSON object key and value strings to uppercase.
%SQLUPPER inserts a space before the JSON object, not before the values
within the object.
Within JSON_OBJECT, you can apply a collation
function to the value portion of a key:value pair. Because %SQLUPPER
inserts a space before the value, it is generally preferable to specify
a case transformation function such as LCASE or UCASE.
ABSENT ON NULL
If you specify the optional ABSENT ON NULL keyword phrase, a
column value which is NULL (or the NULL literal) is not included in
the JSON object. No placeholder is included in the JSON object. This
can result in JSON objects with different numbers of key:value pairs.
For example, the following program returns JSON objects where for
some records the 3rd key:value pair is Age, and for other records
the 3rd key:value pair is FavoriteColors:
SELECT JSON_OBJECT('id':%ID,'name':Name,'colors':FavoriteColors,'years':Age ABSENT ON NULL) FROM Sample.Person
If you specify no keyword phrase, the default is NULL ON NULL:
NULL is represented by the word null (not delimited by quotes) as
the value of the key:value pair. Thus all JSON objects returned by
a JSON_OBJECT function will have the same number
of key:value pairs.
Examples
This example applies JSON_OBJECT to format
a JSON object containing field values:
SELECT TOP 3 JSON_OBJECT('id':%ID,'name':Name,'birth':DOB) FROM Sample.Person
This example applies JSON_OBJECT to format
a JSON object containing literals and field values:
SELECT TOP 3 JSON_OBJECT('lit':'Employee from','t':%TABLENAME,
'name':Name,'num':SSN) FROM Sample.Employee
This example applies JSON_OBJECT to format
a JSON object containing nulls and field values:
SELECT JSON_OBJECT('name':Name,'colors':FavoriteColors) FROM Sample.Person
WHERE Name %STARTSWITH 'S'
This example applies JSON_OBJECT to format
a JSON object containing field values from joined tables:
SELECT TOP 3 JSON_OBJECT('e.t':E.%TABLENAME,'e.name':E.Name,'c.t':C.%TABLENAME,
'c.name':C.Name) FROM Sample.Employee AS E,Sample.Company AS C
The following example uses JSON_OBJECT in
a WHERE clause to perform a Contains test on multiple
columns without using OR syntax:
SELECT Name,Home_City,Home_State FROM Sample.Person
WHERE JSON_OBJECT('name':Name,'city':Home_City,'state':Home_State) [ 'X'
The following Dynamic SQL example sets the ODBC %SelectMode,
which determines how all fields, including JSON object values are
represented. The query overrides this Select Mode for specific JSON_OBJECT values by applying the %EXTERNAL format-transformation function:
SET myquery = 3
SET myquery(1) = "SELECT TOP 8 JSON_OBJECT('ODBCBday':DOB,'DispBday':%EXTERNAL(DOB)),"
SET myquery(2) = "JSON_OBJECT('ODBCcolors':FavoriteColors,'DispColors':%EXTERNAL(FavoriteColors)) "
SET myquery(3) = "FROM Sample.Person"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
WRITE "SelectMode is ODBC",!
SET qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
IF rset.%SQLCODE=0 { WRITE !,"Executed query",! }
ELSE { SET badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE,,rset.%Message)}
DO rset.%Display()
WRITE !,"End of data"
