JSON_ARRAYAGG (SQL)

Synopsis

JSON_ARRAYAGG([ ALL | DISTINCT [BY(col-list)] ] string-expr 
    [ %FOREACH(col-list) ] [ %AFTERHAVING ])

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.

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

• Aggregate Functions overview

• JSON_ARRAY function

• IS JSON predicate condition

• LIST aggregate function

• %DLIST aggregate function

• XMLAGG aggregate function

• SELECT statement