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.

Read syntax diagramSkip visual syntax diagramJSON_OBJECT(,KEYkey-expressionVALUEJSON-expressionFORMATJSONBSONNULL ON NULLABSENT ON NULLWITHOUT UNIQUE KEYSWITH UNIQUE KEYSRETURNING CLOB(2G) FORMAT JSONRETURNINGdata-typeFORMAT JSONENCODING UTF8FORMAT BSON)
data-type
Read syntax diagramSkip visual syntax diagramCHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)CHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)BINARY VARYINGVARBINARY(integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)

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}