The JSON_QUERY function returns an SQL/JSON value from the specified JSON text by using an SQL/JSON path expression.

Although the schema for this function is SYSIBM, the function cannot be specified as a qualified name.

JSON-expression

An expression that returns a value that is a built-in string data type, except the following data types (SQLSTATE 42815):

• GRAPHIC
• VARGRAPHIC
• DBCLOB
• BINARY
• CHAR FOR BIT DATA
• VARCHAR FOR BIT DATA
• A user-defined type that is sourced on any of the previously listed data types

If a character value is returned, it must contain correctly formatted JSON data (SQLSTATE 22032).
If a binary data type is returned, it is interpreted according to the explicit or implicit FORMAT clause.

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).

sql-json-path-expression

An expression that returns a value that is a built-in character string data type.
The string is interpreted as an SQL/JSON path expression and is used to locate a JSON value within the JSON data that is specified by JSON-expression.
For more information about the SQL/JSON path expression, see sql-json-path-expression.

AS path-name

Specifies a name to be used to identify sql-json-path-expression.

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).

WITHOUT ARRAY WRAPPER or WITH ARRAY WRAPPER

Specifies whether the output value is wrapped in a JSON array.

WITHOUT ARRAY WRAPPER

The result is not wrapped. This clause is the default.
Using a strict SQL/JSON path definition that resolves to a sequence of two or more SQL/JSON elements results in an error (SQLSTATE 2203A).
Using a lax SQL/JSON path definition with the ON EMPTY that resolves to a sequence of two or more SQL/JSON elements will result in an error (SQLSTATE 22035).

WITH UNCONDITIONAL ARRAY WRAPPER

The result is enclosed in square brackets to create a JSON array.

WITH CONDITIONAL ARRAY WRAPPER

Indicates that the result is enclosed in square brackets to create a JSON array for either of the following scenarios:

• More than one SQL/JSON element is returned.
• A single SQL/JSON element that is not a JSON array or a JSON object is returned.

KEEP QUOTES or OMIT QUOTES

Specifies whether the surrounding quotation marks should be removed when a scalar string is returned.

KEEP QUOTES

Quotation marks are not removed from scalar strings. This clause is the default.

OMIT QUOTES

Quotation marks are removed from scalar strings.
When OMIT QUOTES is specified, the WITH ARRAY WRAPPER clause cannot be specified (SQLSTATE 42601).

ON EMPTY

Specifies the behavior when an empty sequence is returned by sql-json-path-expression.

NULL ON EMPTY

A null value is returned. This clause is the default.

ERROR ON EMPTY

An error is returned.

EMPTY ARRAY ON EMPTY

An empty array is returned.

EMPTY OBJECT ON EMPTY

An empty object is returned.

ON ERROR

Specifies the behavior when an error is encountered by JSON_QUERY.

NULL ON ERROR

A null value is returned. This clause is the default.

ERROR ON ERROR

An error is returned.

EMPTY ARRAY ON ERROR

An empty array is returned.

EMPTY OBJECT ON ERROR

An empty object is returned.

The result can be null. If JSON-expression is null, the result is the null value.

Notes

• If parameter markers are not explicitly cast to a supported data type, an error is returned (SQLSTATE 42815)

Example

1. Return the JSON object that is associated with the name key from JSON text.

      VALUES JSON_QUERY('{"id":"701", "name":{"first":"John", "last":"Doe"}}', '$.name');

   The result is the following string that represents a JSON object:

      {"first":"John", "last":"Doe"}

See the example at sql-json-path-expression for different array wrapper options with JSON_QUERY.