Chapter 6 SQL aggregate function JSON_ARRAYAGG

An aggregate function that creates an array of JSON formatted values.

Note: IRIS is available, and previous versions of IRIS are not available.

outline

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

parameter

• ALL - optional - specify JSON_ARRAYAGG returns a JSON array containing ALL the values of string expr.
  If no keyword is specified, this is the default value.
• DISTINCT - optional - a DISTINCT clause that specifies JSON_ARRAYAGG returns a JSON array containing only the unique string expr value.
  DISTINCT can specify a BY(colo -list) clause, where colo -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 the column in the selected table.
• %Foreach (Col list) - optional - column names or comma separated list of column names.
• %AFTERHAVING - optional - the condition applied in the HAVING clause.

describe

JSON_ The arrayagg aggregate function returns a JSON formatted array of values in the specified column.

A simple JSON_ Arrayagg (or JSON_ARRAYAGG ALL) returns a JSON array containing all the values of string expr in the selected row.
String - expr lines that are empty strings (") are represented by (" u0000 ") in the array.
Rows with NULL string expr are not included in the array.
If there is only one string - expr value and it is an empty string ("), JSON_ARRAYAGG will return JSON array [" \ u0000 "].
If all string expr values are NULL, JSON_ARRAYAGG returns an empty JSON array [].

JSON_ARRAYAGG DISTINCT returns a JSON array consisting of all different (unique) values of string expr in the selected row: JSON_ARRAYAGG(DISTINCT col1).
NULL string - expr is not contained in JSON array.
JSON_ARRAYAGG(DISTINCT BY(col2) col1) returns a JSON array that contains only those col1 field values whose col2 values in the record are different (unique).
Note, however, that different col2 values may contain a separate NULL value.

JSON_ Arrayagg string expr cannot be a stream field.
The result of specifying the flow field is SQLCODE -37.

Data value containing escape characters

• Double quotation marks: if a string expr value contains a double quotation mark character ("), JSON_ARRAYAGG uses the literal escape sequence \" to represent this character.
• Backslash: JSON if the string expr value contains a backslash character (\)_ Arrayagg uses the literal escape sequence \ \ to represent this character.
• Single quotation mark: when the string expr value contains a single quotation mark as a literal character, SQL requires that this character must be double escaped as two single quotation mark characters (''). JSON_ARRAYAGG represents this character as a single quotation mark character ''.

Maximum JSON array size

Default JSON_ The arrayagg return type is VARCHAR(8192).
This length includes JSON array formatting characters and field data characters.
If the expected return value will need to be greater 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 returned JSON array is longer than JSON_ARRAYAGG returns the type length. IRIS truncates the JSON array at the return type length without issuing an error.
Because truncating the JSON array will delete its closed] characters, this makes the return value invalid.

JSON_ARRAYAGG and% SelectMode

You can use the% SelectMode attribute to specify data display values for elements in the JSON array: 0 = logical (default), 1=ODBC, 2= display.
If string expr contains a% List structure, the elements are represented in ODBC mode, separated by commas, in logic and display mode, in% List format characters, and in \ escape sequence.

JSON_ARRAYAGG and ORDER BY

JSON_ The arrayagg function combines the values of multiple rows and columns in the table into a JSON array containing element values.
Because an ORDER BY clause is applied in the query result set after all aggregate fields are calculated, ORDER BY cannot directly affect the value sequence in this list.
In some cases, JSON_ARRAYAGG results may appear in order, but should not depend on this order.
The values listed in the given aggregate result value cannot be explicitly sorted.

Related aggregate functions

• LIST returns a comma separated LIST of values.
• %DLIST returns an IRIS list of elements containing each value.
• XMLAGG returns a concatenated value string.

Example

The following embedded SQL example returns a host variable that contains the home of the example_ JSON array of all values in the state column.
List of names beginning with A:

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

SELECT JSON_ARRAYAGG(CTLOC_Desc) FROM CT_Loc

Note that this JSON array contains duplicate values.

The following dynamic SQL example returns a host variable that contains the home of the sample_ A JSON array of all different (unique) values in the state column.
List of names beginning with A:

ClassMethod JsonArrayagg()
{
	s myquery = 2
	s myquery(1) = "SELECT JSON_ARRAYAGG(DISTINCT Home_State) AS DistinctStates "
	s myquery(2) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'A'"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=1
	s qStatus = tStatement.%Prepare(.myquery)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
}

The following SQL example creates a JSON array for each state, which is included in home_ All values found in the city column, and a count of these city values by state.
Each home_ The state row contains all the homes for that state_ JSON array of city values.
These JSON arrays may contain duplicate city names:

SELECT Home_State,
       COUNT(Home_City) AS CityCount,
       JSON_ARRAYAGG(Home_City) AS ArrayAllCities 
FROM Sample.Person
GROUP BY Home_State

More useful is a JSON array of all the different values found in Home_City is listed for each state, as shown in the following dynamic SQL example:

ClassMethod JsonArrayagg1()
{
	s myquery = 4
	s myquery(1) = "SELECT Home_State,COUNT(DISTINCT Home_City) AS DistCityCount,"
	s myquery(2) = "COUNT(Home_City) AS TotCityCount,"
	s myquery(3) = "JSON_ARRAYAGG(DISTINCT Home_City) AS ArrayDistCities "
	s myquery(4) = "FROM Sample.Person GROUP BY Home_State"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=1
	s qStatus = tStatement.%Prepare(.myquery)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
}

Note that this example returns an integer count of different city names and total city names for each state.

The following dynamic SQL example uses the% SelectMode property to specify the ODBC display mode for the array of JSON values returned by the DOB date field:

ClassMethod JsonArrayagg2()
{
	s myquery = 2
	s myquery(1) = "SELECT JSON_ARRAYAGG(DOB) AS DOBs "
	s myquery(2) = "FROM Sample.Person WHERE Name %STARTSWITH 'A'"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=1
	s qStatus = tStatement.%Prepare(.myquery)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
}

The following dynamic SQL example uses the% FOREACH keyword.
It's for each different Home_State returns a row containing the home_ JSON array of age values for state.

ClassMethod JsonArrayagg3()
{
	s myquery = 3
	s myquery(1) = "SELECT DISTINCT Home_State,"
	s myquery(2) = "JSON_ARRAYAGG(Age %FOREACH(Home_State)) AgesForState "
	s myquery(3) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'M'"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=1
	s qStatus = tStatement.%Prepare(.myquery)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
}

The following dynamic SQL example uses the% AFTERHAVING keyword.
It's for every Home_State returns a row containing at least one Name value (a Name beginning with "M") that satisfies the condition of the HAVING clause.
First JSON_ The arrayagg function returns a JSON array containing all the names of the state.
Second JSON_ The JSON array returned by the arrayagg function only contains names that meet the conditions of the HAVING clause:

ClassMethod JsonArrayagg4()
{
	s myquery = 4
	s myquery(1) = "SELECT Home_State,JSON_ARRAYAGG(Name) AS AllNames,"
	s myquery(2) = "JSON_ARRAYAGG(Name %AFTERHAVING) AS HavingClauseNames "
	s myquery(3) = "FROM Sample.Person GROUP BY Home_State "
	s myquery(4) = "HAVING Name LIKE 'M%' ORDER BY Home_State"
	s tStatement = ##class(%SQL.Statement).%New()
	s tStatement.%SelectMode=1
	s qStatus = tStatement.%Prepare(.myquery)
	if qStatus'=1 {
		w "%Prepare failed:" 
		d $System.Status.DisplayError(qStatus) 
		q
	}
	s rset = tStatement.%Execute()
	d rset.%Display()
	w !,"End of data"
}