JSON_OBJECT

The JSON_OBJECT function generates a JSON object using the specified key:value pairs. If no key:value pairs are provided, an empty object is returned.

Read syntax diagramSkip visual syntax diagramJSON_OBJECT(,KEYkey-name-expressionVALUEJSON-expressionkey-name-expression : JSON-expressionFORMAT JSONFORMAT BSONNULL 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.
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.
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.
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.

Examples

  • Generate a JSON object for a name.
    VALUES (JSON_OBJECT(KEY 'first' VALUE 'John', KEY 'last' VALUE 'Doe'));
    
    VALUES (JSON_OBJECT('first' : 'John', 'last' : 'Doe'));
    The result of either of these statements is the following JSON string:
    {"first":"John","last":"Doe"}
  • Generate a JSON object containing the last name, date hired, and salary for the employee with an employee number of '000020'.
    SELECT JSON_OBJECT(
                   'Last name' : LASTNAME,
                   'Hire date' : HIREDATE,
                   'Salary'    : SALARY) 
    FROM EMPLOYEE
    WHERE EMPNO  = '000020';
    The result of this statement is the following JSON string:
    {"Last name":"THOMPSON","Hire date":"1973-10-10","Salary":41250.00}