JSON_ARRAYAGG (SQL)
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.