Start of change

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.

Read syntax diagramSkip visual syntax diagramJSON_OBJECTAGG( KEYkey-name-expressionVALUEJSON-expressionkey-name-expression : JSON-expression FORMAT JSONFORMAT BSON NULL ON NULLABSENT ON NULLWITHOUT UNIQUE KEYSWITH UNIQUE KEYS RETURNINGCLOB (2G) CCSID 1208FORMAT JSONRETURNINGdata-typeFORMAT JSONENCODING UTF8ENCODING UTF16 FORMAT BSON )
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
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.
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.
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.
Start of changeWITHOUT UNIQUE KEYS or WITH UNIQUE KEYSEnd of change
Start of changeSpecifies 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.
Generating a JSON object with unique keys is considered the best practice. If key-name-expression generates unique key names, omit WITH UNIQUE KEYS to improve performance.End of change
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.
    SELECT JSON_OBJECTAGG(deptno VALUE mgrno ABSENT ON NULL) FROM dept;
    The result is the following JSON string. Note that the order of entries in the JSON object is not defined.
    {"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.
    SELECT deptno, JSON_OBJECTAGG(projno VALUE projname) AS projlist FROM proj 
WHERE deptno LIKE 'D%'
GROUP BY deptno;
    The result is the following 3 rows. Note that the order of entries in the JSON object is not defined.
    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"}
End of change