Skip to main content

JSON_ARRAYAGG (SQL)

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, InterSystems 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, InterSystems IRIS 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.

Examples

This example returns a JSON array of all values in the Home_State column of the Sample.Person table that start with the letter “A”:

  SELECT JSON_ARRAYAGG(Home_State)
  FROM Sample.Person
  WHERE Home_State %STARTSWITH 'A'

Note that this JSON array contains duplicate values.

The following 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”:

  SELECT DISTINCT JSON_ARRAYAGG(Home_State) AS DistinctStates
  FROM Sample.Person
  WHERE Home_State %STARTSWITH 'A'

The following 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 example:

SELECT DISTINCT Home_State,
       COUNT(DISTINCT Home_City) AS DistCityCount,
       COUNT(Home_City) AS TotCityCount,
       JSON_ARRAYAGG(DISTINCT Home_City) AS ArrayDistCities 
FROM Sample.Person GROUP BY Home_State

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:

  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 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.

SELECT DISTINCT Home_State,
    JSON_ARRAYAGG(Age %FOREACH(Home_State))
FROM Sample.Person
WHERE Home_State %STARTSWITH 'M'

The following 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:

SELECT Home_State,
       JSON_ARRAYAGG(Name) AS AllNames,
       JSON_ARRAYAGG(Name %AFTERHAVING) AS HavingClauseNames 
FROM Sample.Person GROUP BY Home_State
HAVING Name LIKE 'M%' ORDER BY Home_State

See Also

FeedbackOpens in a new tab