JSON_ARRAYAGG

The JSON_ARRAYAGG function returns a JSON array that contains an array element for each value in a set of JSON or SQL values.

Read syntax diagramSkip visual syntax diagram JSON_ARRAYAGG ( JSON-expression FORMAT JSONFORMAT BSONORDER BY,sort-key-expressionASCDESC ABSENT ON NULLNULL ON NULL RETURNINGCLOB (2G) CCSID 1208FORMAT JSONRETURNINGdata-typeFORMAT JSONENCODING UTF8 )
data-type
Read syntax diagramSkip visual syntax diagramCHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)CHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)BINARY VARYINGVARBINARY(integer)BINARY LARGE OBJECTBLOB(1M)(integerKMG)
Note: The schema is SYSIBM. The function name cannot be specified as a qualified name.
JSON-expression
The expression to use to generate a value in the JSON array. The result type of this expression can be any built-in data type. It cannot be CHAR or VARCHAR bit data. It cannot be a user-defined type that is sourced on any of these data types.
FORMAT JSON or FORMAT BSON
Specifies whether the JSON expression is already formatted data.
FORMAT JSON
The JSON expression is formatted as JSON data. If the JSON expression is a character or graphic string data type, it is treated as JSON data. If the JSON expression is a binary string data type, it is interpreted as UTF-8 data.
FORMAT BSON
The JSON expression is formatted as the BSON representation of JSON data and must be a binary string data type.
If either the FORMAT JSON or FORMAT BSON properties are omitted from the statement, the format of the JSON expression is determined in the following ways:
  • By the explicit or implicit FORMAT value of the JSON expression. This method applies when the JSON expression is one of the following built-in functions:
    • JSON_ARRAY
    • JSON_OBJECT
    • JSON_QUERY
    • JSON_ARRAYAGG
    • JSON_OBJECTAGG
  • As FORMAT BSON when the JSON expression is a binary string type.
  • As unformatted data in all other situations. If the generated value is not numeric, the result string is constructed with strings that are enclosed in quotation marks, and escape sequences for any special characters. A numeric value that is not a valid JSON number, such as INFINITY or NAN, results in an error.
ORDER BY
Specifies the order of the rows from the same grouping set that is processed in the aggregation. Rows in the same grouping are arbitrarily ordered when the ORDER BY clause is not specified, or if the ORDER BY clause cannot differentiate the order of the sort key value.
sort-key-expression
Specifies a sort key value that is either a column name or an expression. The data type of the column or expression must not be a DATALINK or XML value.
ASC
Processes the sort key expression in ascending order. This setting is the default.
DESC
Processes the sort key expression in descending order.

The ordering is based on the values of the sort keys, which might or might not be used in the JSON expression.

When you are running a statement that contains the JSON_ARRAYAGG function, weighted values are sometimes used to return the result. These values are used under the following circumstances:
  • When a collating sequence other than *HEX is in effect.
  • When the sort-key-expressions contain SBCS data, mixed data, or Unicode data.

The weighted values are derived by applying the collating sequence to the sort key expressions.

ABSENT ON NULL or NULL ON NULL
Specifies what to return when an array element that is produced by the JSON expression is the null value.
ABSENT ON NULL
A null array element is not included in the JSON array. This setting is the default.
NULL ON NULL
A null array element is included in the JSON array.
RETURNING data-type
Specifies the format of the result.
data-type
The data type of the result. The default data type is Character Large Object [CLOB (2G)]
FORMAT JSON
JSON data is returned as a JSON string.
ENCODING UTF8
The encoding to use when data-type is a binary string type. This clause is only allowed for binary string types. The result can be null. If the set of values is empty, the result is the null value.

The result can be null. If the set of values is empty, the result is the null value.

Notes

The JSON_ARRAYAGG aggregate function cannot be used as part of an OLAP specification.

Example

  • The following example shows the command syntax for returning a JSON array containing all the department numbers.
    SELECT JSON_ARRAYAGG(deptno) AS deptlist FROM dept;
    The result is the following JSON array.
    DEPT_NAME    
------------------------------ 
["sales","Procurment","finance","Eng","Design","Labour"]    
 
1 record(s) selected.
  • The following example shows the command syntax for returning a JSON array for each department that contains a list of employees that are assigned to that department. 
    SELECT deptno, JSON_ARRAYAGG(id) AS employe_id from employe group by deptno
    The result is the following two rows.
    DEPTN  EMPLOYE_ID 
---------- -------------------------------------- 
        102 [102001,102002,102003,102004]     
        103 [103001,103002,103003,103004]           
       2 record(s) selected.
  • The following example shows the command syntax for returning a JSON array for each department number and its corresponding department name.
    SELECT num as dept_num, JSON_ARRAYAGG (deptname) as dept_name FROM dept group by num
    The result is the following JSON array.
    DEPT_NUM    DEPT_NAME   
----------- -------------------------------------- 
        101 ["sales"]  
        102 ["Procurment"]  
        103 ["finance"]  
        104 ["Eng"] 
        105 ["Design"]  
        106 ["Labour"] 
6 record(s) selected.