JSON_QUERY scalar function

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

Read syntax diagramSkip visual syntax diagram JSON_QUERY(JSON-expressionFORMAT JSONFORMAT BSON, sql-json-path-expression ASpath-name RETURNING CLOB(2G) FORMAT JSONRETURNINGdata-typeFORMAT JSONENCODING UTF8FORMAT BSON WITHOUTARRAYWRAPPERWITHUNCONDITIONALCONDITIONALARRAYWRAPPER KEEP QUOTESON SCALAR STRINGOMIT QUOTESON SCALAR STRING NULL ON EMPTYERROREMPTY ARRAYEMPTY OBJECTON EMPTYNULL ON ERRORERROREMPTY ARRAYEMPTY OBJECTON ERROR)
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.

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.