Skip to main content

JSON_ARRAY (SQL)

A conversion function that returns data as a JSON array.

Synopsis

JSON_ARRAY(expression [,expression][,...] 
  [NULL ON NULL | ABSENT ON NULL])

Description

JSON_ARRAY takes an expression or (more commonly) a comma-separated list of expressions and returns a JSON array containing those values. JSON_ARRAY can be combined in a SELECT statement with other types of select-items. JSON_ARRAY can be specified in other locations where an SQL function can be used, such as in a WHERE clause.

The returned JSON array has the following format:

[ element1 , element2 , element3 ]

JSON_ARRAY returns each array element value 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.

JSON_ARRAY does not support asterisk (*) syntax as a way to specify all fields in a table. It does support the COUNT(*) aggregate function.

The returned JSON array column is labeled as an Expression (by default); you can specify a column alias for a JSON_ARRAY.

Select Mode and Collation

The current %SelectMode property determines the format of the returned JSON array values. By changing the Select Mode, all Date and %List elements are included in the JSON array 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_ARRAY. Applying a format-transformation function to a JSON_ARRAY has no effect, because the elements of a JSON array are strings.

You can apply a collation function to individual field names within JSON_ARRAY or to an entire JSON_ARRAY:

  • A collation function applied to a JSON_ARRAY applies the collation after JSON array formatting. Therefore, %SQLUPPER(JSON_ARRAY(f1,f2)) converts all the JSON array element values to uppercase. %SQLUPPER(JSON_ARRAY(f1,f2)) inserts a space before the JSON array, not before the elements of the array; therefore it does not force numbers to be parsed as strings.

  • A collation function applied to an element within a JSON_ARRAY applies that collation. Therefore JSON_ARRAY('Abc',%SQLUPPER('Abc')) returns ["Abc"," ABC"] (note leading space); and JSON_ARRAY(007,%SQLSTRING(007)) returns [7," 7"]. Because %SQLUPPER inserts a space before the value, it is generally preferable to specify a case transformation function such as LCASE or UCASE. You can apply collation to both an element and to the whole array: %SQLUPPER(JSON_ARRAY('Abc',%SQLSTRING('Abc'))) returns ["ABC"," ABC"]

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 array. No placeholder is included in the JSON array. This can result in JSON arrays with different numbers of elements. For example, the following program returns JSON arrays where for some records the 3rd array element is Age, and for other records the 3rd element is FavoriteColors:

SELECT JSON_ARRAY(%ID,Name,FavoriteColors,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 a comma-separated array element. Thus all JSON arrays returned by a JSON_ARRAY function will have the same number of array elements.

Arguments

expression

An expression or a comma-separated list of expressions. These expressions can include column names, aggregate functions, arithmetic expressions, literals, and the literal NULL.

ABSENT ON NULL/NULL ON NULL

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

Examples

The following example applies JSON_ARRAY to format a JSON array containing a comma-separated list of field values:

SELECT TOP 3 JSON_ARRAY(%ID,Name,Age,Home_State) FROM Sample.Person

The following example applies JSON_ARRAY to format a JSON array with a single element containing the Name field values:

SELECT TOP 3 JSON_ARRAY(Name) FROM Sample.Person

The following example applies JSON_ARRAY to format a JSON array containing literals and field values:

SELECT TOP 3 JSON_ARRAY('Employee from',%TABLENAME,Name,SSN) FROM Sample.Employee

The following example applies JSON_ARRAY to format a JSON array containing nulls and field values:

SELECT JSON_ARRAY(Name,FavoriteColors) FROM Sample.Person
WHERE Name %STARTSWITH 'S'

The following example applies JSON_ARRAY to format a JSON array containing field values from joined tables:

SELECT TOP 3 JSON_ARRAY(E.%TABLENAME,E.Name,C.%TABLENAME,C.Name) 
FROM Sample.Employee AS E,Sample.Company AS C

The following Dynamic SQL example sets the ODBC %SelectMode, which determines how all fields, including JSON array values are represented. The query overrides this Select Mode for specific JSON array elements by applying the %EXTERNAL format-transformation function:

  SET myquery = 3
    SET myquery(1) = "SELECT TOP 8 DOB,JSON_ARRAY(Name,DOB,FavoriteColors) AS ODBCMode, "
    SET myquery(2) = "JSON_ARRAY(Name,DOB,%EXTERNAL(DOB),%EXTERNAL(FavoriteColors)) AS ExternalTrans "
    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 example uses JSON_ARRAY 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_ARRAY(Name,Home_City,Home_State) [ 'X'

See Also

FeedbackOpens in a new tab