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])

Arguments

Argument	Description
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	Optional — A 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.

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

The following Dynamic SQL example applies JSON_OBJECT to format a JSON object containing field values:

  SET myquery = 2
  SET myquery(1) = "SELECT TOP 3 JSON_OBJECT('id':%ID,'name':Name,'birth':DOB,"
  SET myquery(2) = "'age':Age,'state':Home_State) FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {DO rset.%Print(" ^ ")}
  WRITE !,"Total row count=",rset.%ROWCOUNT

The following Dynamic SQL example applies JSON_OBJECT to format a JSON object containing literals and field values:

  SET myquery = 2
  SET myquery(1) = "SELECT TOP 3 JSON_OBJECT('lit':'Employee from','t':%TABLENAME,"
  SET myquery(2) = "'name':Name,'num':SSN) FROM Sample.Employee"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {DO rset.%Print(" ^ ")}
  WRITE !,"Total row count=",rset.%ROWCOUNT

The following Dynamic SQL example applies JSON_OBJECT to format a JSON object containing nulls and field values:

  SET myquery = 2
  SET myquery(1) = "SELECT JSON_OBJECT('name':Name,'colors':FavoriteColors) FROM Sample.Person"
  SET myquery(2) = " WHERE Name %STARTSWITH 'S'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {DO rset.%Print(" ^ ")}
  WRITE !,"Total row count=",rset.%ROWCOUNT

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"

The following Dynamic SQL example applies JSON_OBJECT to format a JSON object containing field values from joined tables:

  SET myquery = 2
  SET myquery(1) = "SELECT TOP 3 JSON_OBJECT('e.t':E.%TABLENAME,'e.name':E.Name,'c.t':C.%TABLENAME,"
  SET myquery(2) = "'c.name':C.Name) FROM Sample.Employee AS E,Sample.Company AS C"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {DO rset.%Print(" ^ ")}
  WRITE !,"Total row count=",rset.%ROWCOUNT

The following Dynamic SQL example uses JSON_OBJECT in a WHERE clause to perform a Contains test on multiple columns without using OR syntax:

  SET myquery = 2
  SET myquery(1) = "SELECT Name,Home_City,Home_State FROM Sample.Person"
  SET myquery(2) = " WHERE JSON_OBJECT('name':Name,'city':Home_City,'state':Home_State) [ 'X'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {DO rset.%Print(" ^ ")}
  WRITE !,"Total row count=",rset.%ROWCOUNT