Caché SQL Reference
JSON_ARRAYAGG
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

An aggregate function that creates a JSON format array of values.
Synopsis
JSON_ARRAYAGG([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])
Arguments
ALL Optional — Specifies that JSON_ARRAYAGG returns a JSON array containing all values for string-expr. This is the default if no keyword is specified.
DISTINCT Optional — A DISTINCT clause that specifies that JSON_ARRAYAGG returns a JSON array containing only the unique string-expr values. DISTINCT can specify a BY(col-list) subclause, where col-list can be a single field or a comma-separated list of fields.
string-expr An SQL expression that evaluates to a string. Usually the name of a column from the selected table.
%FOREACH(col-list) Optional — A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.
%AFTERHAVING Optional — Applies the condition found in the HAVING clause.
Description
The JSON_ARRAYAGG aggregate function returns a JSON format array of the values in the specified column. For further details on JSON array format, refer to the JSON_ARRAY function.
A simple JSON_ARRAYAGG (or JSON_ARRAYAGG ALL) returns a JSON array containing all the values for string-expr in the selected rows. Rows where string-expr is the empty string ('') are represented by ("\u0000") in the array. Rows where string-expr is NULL are not included in the array. If there is only one string-expr value, and it is the empty string (''), JSON_ARRAYAGG returns the JSON array ["\u0000"]. If all string-expr values are NULL, JSON_ARRAYAGG returns an empty JSON array [].
A JSON_ARRAYAGG DISTINCT returns a JSON array composed of all the different (unique) values for string-expr in the selected rows: JSON_ARRAYAGG(DISTINCT col1). The NULL string-expr is not included in the JSON array. JSON_ARRAYAGG(DISTINCT BY(col2) col1) returns a JSON array containing only those col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.
The JSON_ARRAYAGG string-expr cannot be a stream field. Specifying a stream field results in an SQLCODE -37.
Data Values Containing Escaped Characters
Maximum JSON Array Size
The default JSON_ARRAYAGG return type is VARCHAR(8192). This length includes the JSON array formatting characters as well as the field data characters. If you anticipate the value returned will need to be longer than 8192, you can use the CAST function to specify a larger return value. For example, CAST(JSON_ARRAYAGG(value)) AS VARCHAR(12000)). If the actual JSON array returned is longer than the JSON_ARRAYAGG return type length, Caché truncates the JSON array at the return type length without issuing an error. Because truncating a JSON array removes its closing ] character, this makes the return value invalid.
JSON_ARRAYAGG and %SelectMode
You can use the %SelectMode property to specify the data display values for the elements in the JSON array: 0=Logical (the default), 1=ODBC, 2=Display. If the string-expr contains a %List structure, the elements are represented in ODBC mode separated by a comma, and in Logical and Display mode with %List format characters represented by \ escape sequences. Refer to $ZCONVERT “Encoding Translation” for an table listing these JSON \ escape sequences.
JSON_ARRAYAGG and ORDER BY
The JSON_ARRAYAGG array element values are, by default, listed in the RowId sequence. You can use an ORDER BY clause to sequence these JSON array values in ascending collation sequence. This use of ORDER BY is subject to various conditions and restrictions, as described in the With ORDER BY section of the Aggregate Functions overview page.
Related Aggregate Functions
Examples
The following Embedded SQL example returns a host variable containing a JSON array of all of the values in the Home_State column of the Sample.Person table that start with the letter “A”:
  &sql(SELECT JSON_ARRAYAGG(Home_State)
       INTO :statearray
       FROM Sample.Person
       WHERE Home_State %STARTSWITH 'A')
  WRITE "JSON array of states:",!,statearray
 
Note that this JSON array contains duplicate values.
The following Dynamic SQL example returns a host variable containing a JSON array of all of the distinct (unique) values in the Home_State column of the Sample.Person table that start with the letter “A”:
  ZNSPACE "SAMPLES"
  SET myquery = 2
  SET myquery(1) = "SELECT JSON_ARRAYAGG(DISTINCT Home_State) AS DistinctStates "
  SET myquery(2) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'A'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
 
The following SQL example creates a JSON array of all of the values found in the Home_City column for each of the states, and a count of these city values by state. Every Home_State row contains a JSON array of all of the Home_City values for that state. These JSON arrays may include duplicate city names:
SELECT Home_State,
       COUNT(Home_City) AS CityCount,
       JSON_ARRAYAGG(Home_City) AS ArrayAllCities 
FROM Sample.Person
GROUP BY Home_State
 
Perhaps more useful would be a JSON array of all of the distinct values found in the Home_City column for each of the states, as shown in the following Dynamic SQL example:
  ZNSPACE "SAMPLES"
  SET myquery = 4
  SET myquery(1) = "SELECT Home_State,COUNT(DISTINCT Home_City) AS DistCityCount,"
  SET myquery(2) = "COUNT(Home_City) AS TotCityCount,"
  SET myquery(3) = "JSON_ARRAYAGG(DISTINCT Home_City) AS ArrayDistCities "
  SET myquery(4) = "FROM Sample.Person GROUP BY Home_State"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
 
Note that this example returns integer counts of both the distinct city names and the total city names for each state.
The following Dynamic SQL example uses the %SelectMode property to specify the ODBC display mode for the JSON array of values returned by the DOB date field:
  ZNSPACE "SAMPLES"
  SET myquery = 2
  SET myquery(1) = "SELECT JSON_ARRAYAGG(DOB) AS DOBs "
  SET myquery(2) = "FROM Sample.Person WHERE Name %STARTSWITH 'A'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
 
The following Dynamic SQL example uses the %FOREACH keyword. It returns a row for each distinct Home_State containing a JSON array of age values for that Home_State.
  ZNSPACE "SAMPLES"
  SET myquery = 3
  SET myquery(1) = "SELECT DISTINCT Home_State,"
  SET myquery(2) = "JSON_ARRAYAGG(Age %FOREACH(Home_State)) AgesForState "
  SET myquery(3) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'M'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
 
The following Dynamic SQL example uses the %AFTERHAVING keyword. It returns a row for each Home_State that contains at least one Name value that fulfills the HAVING clause condition (a name that begins with “M”). The first JSON_ARRAYAGG function returns a JSON array of all of the names for that state. The second JSON_ARRAYAGG function returns a JSON array containing only those names that fulfill the HAVING clause condition:
  ZNSPACE "SAMPLES"
  SET myquery = 4
  SET myquery(1) = "SELECT Home_State,JSON_ARRAYAGG(Name) AS AllNames,"
  SET myquery(2) = "JSON_ARRAYAGG(Name %AFTERHAVING) AS HavingClauseNames "
  SET myquery(3) = "FROM Sample.Person GROUP BY Home_State "
  SET myquery(4) = "HAVING Name LIKE 'M%' ORDER BY Home_State"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
 
See Also