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.
- 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.
Examples
- Generate a JSON object for a
name.
The result of either of these statements is the following JSON string:VALUES (JSON_OBJECT(KEY 'first' VALUE 'John', KEY 'last' VALUE 'Doe')); VALUES (JSON_OBJECT('first' : 'John', 'last' : 'Doe'));
{"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'.
The result of this statement is the following JSON string:SELECT JSON_OBJECT( 'Last name' : LASTNAME, 'Hire date' : HIREDATE, 'Salary' : SALARY) FROM EMPLOYEE WHERE EMPNO = '000020';
{"Last name":"THOMPSON","Hire date":"1973-10-10","Salary":41250.00}