Start of change

JSON_ARRAYAGG

The JSON_ARRAYAGG function returns a JSON array containing 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 UTF8ENCODING UTF16 )
data-type
Read syntax diagramSkip visual syntax diagramCHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)normalize-clauseBINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BINARY LARGE OBJECTBLOB(1M)(integerKMG)
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDintegernormalize-clause
normalize-clause
Read syntax diagramSkip visual syntax diagramNOT NORMALIZEDNORMALIZED
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 except XML, ROWID, or DATALINK. 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 JSON-expression is already formatted data.
FORMAT JSON
JSON-expression is formatted as JSON data. If JSON-expression is a character or graphic string data type, it is treated as JSON data. If JSON-expression is a binary string data type, it is interpreted as UTF-8 or UTF-16 data.
FORMAT BSON
JSON-expression is formatted as the BSON representation of JSON data and must be a binary string data type.
If neither FORMAT JSON nor FORMAT BSON is specified:
  • If JSON-expression is one of the built-in functions JSON_ARRAY, JSON_OBJECT, JSON_QUERY, JSON_ARRAYAGG, or JSON_OBJECTAGG, the explicit or implicit FORMAT value of the function's RETURNING clause determines the format of JSON-expression.
  • If JSON-expression is a binary string type, it is interpreted as FORMAT BSON.
  • Otherwise, JSON-expression is considered unformatted data. If the generated value is not numeric, the result string will be constructed with strings enclosed in quotes and any special characters will be escaped. A numeric value that is not a valid JSON number, such as INFINITY or NAN, will result in an error.
ORDER BY
Specifies the order of the rows from the same grouping set that are processed in the aggregation. If the ORDER BY clause is not specified, or if the ORDER BY clause cannot differentiate the order of the sort key value, the rows in the same grouping set are arbitrarily ordered.
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 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 JSON-expression.

The sum of the length attributes of the sort-key-expressions must not exceed 3.5 gigabytes.

If a collating sequence other than *HEX is in effect when the statement that contains the JSON_ARRAYAGG function is executed and the sort-key-expressions are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values. 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 produced by JSON-expression is the null value.
ABSENT ON NULL
A null array element is not included in the JSON array. This 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. For CHAR and VARCHAR results, the CCSID cannot be 65535. The default is CLOB(2G) CCSID 1208.

If a CCSID is specified and the data-type is GRAPHIC, VARGRAPHIC, or DBCLOB, the CCSID must be a Unicode CCSID.

If the CCSID attribute is not specified, the CCSID is determined as described in CAST specification.

FORMAT JSON
JSON data is returned as a JSON string.
ENCODING UTF8 or ENCODING UTF16
The encoding to use when data-type is a binary string type. This clause is only allowed for binary string types. The default for binary strings is UTF8.

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

Example

  • Return a JSON array containing all the department numbers.
    SELECT JSON_ARRAYAGG(deptno) AS deptlist FROM dept;
    The result is the following JSON array.
    ["A00","B01","C01","D01","D11","D21","E01","E11","E21","F22","G22","H22","I22","J22"]
  • Return a JSON array for each department containing a list of employees assigned to that department. 
    SELECT workdept, JSON_ARRAYAGG(lastname ORDER BY lastname) AS dept_employees 
FROM emp 
WHERE workdept LIKE 'D%'
GROUP BY workdept;
    The result is the following 2 rows.
    DEPTNO      PROJLIST
 D11        ["ADAMSON","BROWN","JOHN","JONES","LUTZ","PIANKA","SCOUTTEN",
             "STERN","WALKER","YAMAMOTO","YOSHIMURA"]
 D21        ["JEFFERSON","JOHNSON","MARINO","MONTEVERDE","PEREZ","PULASKI","SMITH"]
  • Return a JSON object that contains a list of departments and employees in each department.
    SELECT JSON_OBJECT('department number' VALUE deptno,
                   'department name' VALUE deptname,
                   'employee list' VALUE 
                        JSON_ARRAYAGG(
                              JSON_OBJECT('last name' VALUE lastname,
                                          'employee id' VALUE empno)
                              ORDER BY lastname)) 
 FROM dept LEFT OUTER JOIN emp ON deptno = workdept
 WHERE deptno LIKE 'D%'
 GROUP BY deptno, deptname;
    The result is the following 3 rows.
    {"department number":"D11","department name":"MANUFACTURING SYSTEMS",
    "employee list":[{"last name":"ADAMSON","employee id":"000150"},
                     {"last name":"BROWN","employee id":"000200"},
                     {"last name":"JOHN","employee id":"200220"},
                     {"last name":"JONES","employee id":"000210"},
                     {"last name":"LUTZ","employee id":"000220"},
                     {"last name":"PIANKA","employee id":"000160"},
                     {"last name":"SCOUTTEN","employee id":"000180"},
                     {"last name":"STERN","employee id":"000060"},
                     {"last name":"WALKER","employee id":"000190"},
                     {"last name":"YAMAMOTO","employee id":"200170"},
                     {"last name":"YOSHIMURA","employee id":"000170"}]}

{"department number":"D21","department name":"ADMINISTRATION SYSTEMS",
    "employee list":[{"last name":"JEFFERSON","employee id":"000150"},
                      "last name":"JOHNSON","employee id":"000150"},
                      "last name":"MARINO","employee id":"000150"},
                      "last name":"MONTEVERDE","employee id":"000150"},
                      "last name":"PEREZ","employee id":"000150"},
                      "last name":"PULASKI","employee id":"000150"},
                      "last name":"SMITH","employee id":"000150"}]}

{"department number":"D01","department name":"DEVELOPMENT CENTER"}
End of change