Skip to main content

JSON_ARRAYAGG

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

Argument Description
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

  • Double Quote: If a string-expr value contains a double quote character ("), JSON_ARRAYAGG represents this character using the literal escape sequence \".

  • Backslash: If a string-expr value contains a backslash character (\), JSON_ARRAYAGG represents this character using the literal escape sequence \\.

  • Single Quote: When a string-expr value contains a single quote as a literal character, Caché SQL requires that this character must be escaped by doubling it as two single quote characters (''. JSON_ARRAYAGG represents this character as a single quote character '.

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 function combines the values of a table column from multiple rows into a JSON array of element values. Because an ORDER BY clause is applied to the query result set after all aggregate fields are evaluated, ORDER BY cannot directly affect the sequence of values within this list. Under certain circumstances, JSON_ARRAYAGG results may appear in sequential order, but this ordering should not be relied upon. The values listed within a given aggregate result value cannot be explicitly ordered.

Related Aggregate Functions

  • LIST returns a comma-separated list of values.

  • %DLIST returns a Caché list containing an element for each value.

  • XMLAGG returns a concatenated string of values.

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

FeedbackOpens in a new tab