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

1. 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"}

2. 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}