A conversion function that returns data as a JSON array.
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.
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'
