JSON_OBJECT (SQL)

A conversion function that returns data as a JSON object.

Synopsis

JSON_OBJECT(key:value [,key:value][,...] 
  [NULL ON NULL | ABSENT ON NULL])

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. This means that NULL is represented by the literal 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 a value for every key. If the value is missing, it will return null.

Arguments

key:value

A key:value pair or a comma-separated list of key:value pairs. A key is a user-specified literal string delimited with single quotes. A value can be a column name, an aggregate function, an arithmetic expression, a numeric or string literal, or the literal NULL.

ABSENT ON NULL/NULL ON NULL

An optional keyword phrase specifying how to represent NULL values in the returned JSON object. NULL ON NULL (the default) represents NULL (absent) data with the word null (not quoted). ABSENT ON NULL omits NULL data from the JSON object; it removes the key:value pair when value is NULL and does not leave a placeholder comma. This keyword phrase has no effect on empty string values.

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"