JSON_OBJECT scalar function
The JSON_OBJECT function generates a JSON object by using the specified key:value pairs. If no key:value pairs are provided, an empty object is returned.
Although the schema for this function is SYSIBM, the function cannot be specified as a qualified name.
- key-expression
- The name of the JSON key. The name must not be null (SQLSTATE 22004). The result of
key-expression must be a built-in character string data type, except the
following data types (SQLSTATE 42815):
- GRAPHIC
- VARGRAPHIC
- DBCLOB
- CHAR FOR BIT DATA
- VARCHAR FOR BIT DATA
- JSON-expression
- The expression that is used to generate the JSON value that is associated with
key-expression. The result type of this expression can be any built-in data type, except the following data types (SQLSTATE 42815):
- GRAPHIC
- VARGRAPHIC
- DBCLOB
- BINARY
- CHAR FOR BIT DATA
- VARCHAR FOR BIT DATA
- XML
- A user-defined type that is sourced on any of the previously listed data types
If the generated value is numeric, it cannot be Infinity, NaN, or sNaN (SQLSTATE 22023).
If FORMAT JSON or FORMAT BSON is not specified, and the generated value is not numeric, any special characters (for example, backslash or double quotation marks) within the result string are escaped.
If FORMAT JSON or FORMAT BSON is not specified and the expression is binary string type, it is interpreted as FORMAT BSON.
- FORMAT JSON
- JSON-expression is formatted as JSON data.
If JSON-expression is a character string data type, it is treated as JSON data.
If JSON-expression is a binary string data type, it is interpreted as UTF-8 data.
- FORMAT BSON
- Specifies that JSON-expression is formatted as the BSON representation of JSON data (SQLSTATE 22032). JSON-expression must be a binary string data type (SQLSTATE 42815).
- ON NULL
- Specifies what to return when JSON-expression is the null value.
- NULL ON NULL
- A character string that represents the null value is returned. This clause 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 is not checked for duplicate keys. This clause is the default.
- WITH UNIQUE KEYS
- The resulting JSON object must have unique key values (SQLSTATE 22037).
Generating a JSON object with unique keys is considered best practice. If key-expression generates unique key names, omit WITH UNIQUE KEYS to improve performance.
- RETURNING data-type
- The data type of the result can be CHAR, VARCHAR, CLOB, VARBINARY, or BLOB (SQLSTATE 42815). The
default is CLOB (2 GB).
See CREATE TABLE statement for the description of built-in data types.
- FORMAT JSON
- The returned data is formatted as JSON data.
- ENCODING UTF8
- Specifies the encoding to use when data-type is a binary string type. This clause is allowed only for binary string types. The default for binary strings is UTF-8.
- FORMAT BSON
- The returned data is formatted as the BSON representation of JSON data (SQLSTATE 22032). data-type must be a binary string data type (SQLSTATE 42815).
Notes
- If parameter markers are not explicitly cast to a supported data type, an error is returned (SQLSTATE 42815)
Examples
- Generate a JSON object for a person’s
name.
VALUES JSON_OBJECT(KEY 'first' VALUE 'John', KEY 'last' VALUE 'Doe') 1 ----------------------------- {"first":"John","last":"Doe"}
- Generate a JSON object that contains the surname, hire date, and salary for the employee with an
employee number of
'000020'.
SELECT JSON_OBJECT(KEY 'Last name' VALUE LASTNAME, KEY 'Hire date' VALUE HIREDATE, KEY 'Salary' VALUE SALARY) FROM EMPLOYEE WHERE EMPNO = '000020'; 1 ------------------------------------------------------------------- {"Last name":"THOMPSON","Hire date":"1973-10-10","Salary":41250.00}