JSON_OBJECTAGG
The JSON_OBJECTAGG function returns a JSON object containing a key:value pair for each specific key and value in a set of SQL values.
- key-name-expression
- The name of the JSON key. The name must not be null. When using the colon form for defining a key:value pair, key-name-expression must be a character string literal. Otherwise, the result of key-name-expression must be a built-in character or graphic string data type. It cannot be CHAR or VARCHAR bit data.
- JSON-expression
- The expression to use to generate the JSON value associated with key-name-expression. 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.
- NULL ON NULL or ABSENT ON NULL
- Specifies what to return when JSON-expression is the null value.
- NULL ON NULL
- A null value is returned. This is the default.
- ABSENT ON NULL
- The key:value pair is omitted from the JSON object.
- WITHOUT UNIQUE KEYS or WITH UNIQUE KEYS
- Specifies whether the key values for the resulting JSON object must be unique.
- WITHOUT UNIQUE KEYS
- The resulting JSON object will not be checked for duplicate keys. This is the default.
- WITH UNIQUE KEYS
- The resulting JSON object is required to have unique key values. An error will be issued if duplicate keys are generated.
- 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.
- FORMAT BSON
- JSON data is returned in BSON format. When FORMAT BSON is specified, data-type must be a VARBINARY or BLOB string type.
The result can be null. If the set of values is empty, the result is the null value.
Example
- Return a JSON object containing the manager numbers for each department.
If no manager is assigned, omit the department from the result.
The result is the following JSON string. Note that the order of entries in the JSON object is not defined.SELECT JSON_OBJECTAGG(deptno VALUE mgrno ABSENT ON NULL) FROM dept;
{"A00":"000010","B01":"000020","C01":"000030","D11":"000060","D21":"000070", "E01":"000050","E11":"000090","E21":"000100"}
- Return a JSON object for each department containing a list of
projects assigned to that department.
The result is the following 3 rows. Note that the order of entries in the JSON object is not defined.SELECT deptno, JSON_OBJECTAGG(projno VALUE projname) AS projlist FROM proj WHERE deptno LIKE 'D%' GROUP BY deptno;
DEPTNO PROJLIST D01 {"AD3100":"ADMIN SERVICES","MA2100":"WELD LINE AUTOMATION"} D11 {"MA2110":"W L PROGRAMMING","MA2111":"W L PROGRAM DESIGN", "MA2112":"W L ROBOT DESIGN","MA2113":"W L PROD CONT PROGS"} D21 {"AD3110":"GENERAL ADMIN SYSTEMS","AD3111":"PAYROLL PROGRAMMING", "AD3112":"PERSONNEL PROGRAMMING","AD3113":"ACCOUNT PROGRAMMING"}