Start of change

JSON_QUERY

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 diagramJSON_QUERY( JSON-expressionFORMAT JSONFORMAT BSON ,sql-json-path-expression ASpath-name RETURNINGCLOB (2G) CCSID 1208FORMAT JSONRETURNINGdata-typeFORMAT JSONENCODING UTF8ENCODING UTF16 FORMAT BSONWITHOUTARRAYWRAPPERWITHUNCONDITIONALCONDITIONALARRAYWRAPPERKEEP QUOTESON SCALAR STRINGOMIT QUOTESON SCALAR STRING NULL ON EMPTYERROREMPTY ARRAYEMPTY OBJECTON EMPTY NULL ON ERRORERROREMPTY ARRAYEMPTY OBJECTON ERROR )
data-type
Read syntax diagramSkip visual syntax diagramCHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)normalize-clauseBINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BINARY LARGE OBJECTBLOB(1M)(integerKMG)
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDintegernormalize-clause
normalize-clause
Read syntax diagramSkip visual syntax diagramNOT NORMALIZEDNORMALIZED
JSON-expression
An expression that returns a value that is a built-in string data type. If a character or graphic value is returned, it must contain correctly formatted JSON data. If it is a binary data type, it is interpreted according to the explicit or implicit FORMAT clause.
FORMAT JSON or FORMAT BSON
Specifies how JSON-expression is to be interpreted.
FORMAT JSON
JSON-expression contains JSON data. If JSON-expression is binary data, the data is interpreted as UTF-8 or UTF-16. Binary data cannot be encoded using an EBCDIC CCSID.
FORMAT BSON
JSON-expression contains the BSON representation of JSON data. When FORMAT BSON is specified, JSON-expression must be a binary string data type.
If the FORMAT clause is not specified and JSON-expression is a character or graphic string, JSON-expression is treated as JSON. If JSON-expression is a binary string, JSON-expression is treated as BSON.
sql-json-path-expression
An expression that returns a value that is a built-in character or graphic 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 specified by JSON-expression. Start of changeIf more than one value has the same key, one of the JSON values will be selected.End of change
For information on the content of an SQL/JSON path expression, see sql-json-path-expression.
AS path-name
Specifies a name to be used to identify the sql-json-path-expression.
RETURNING data-type
Specifies the format of the result.
data-type
The data type of the result. For CHAR and VARCHAR results, the CCSID cannot be 65535. The default is CLOB(2G) CCSID 1208.

If a CCSID is specified and the data-type is GRAPHIC, VARGRAPHIC, or DBCLOB, the CCSID must be a Unicode CCSID.

If the CCSID attribute is not specified, the CCSID is determined as described in CAST specification.

FORMAT JSON
JSON data is returned as a JSON string.
ENCODING UTF8 or ENCODING UTF16
The encoding to use when data-type is a binary string type. This clause is only allowed for binary string types. The default for binary strings is UTF8.
FORMAT BSON
JSON data is returned in BSON format. When FORMAT BSON is specified, data-type must be a VARBINARY or BLOB string type. FORMAT BSON can only be used when an SQL/JSON object is returned.
WITHOUT ARRAY WRAPPER or WITH ARRAY WRAPPER
Specifies whether the output value should be wrapped in a JSON array.
WITHOUT ARRAY WRAPPER
The result is not wrapped. This is the default. Using an SQL/JSON path that results in a sequence of two or more SQL/JSON elements results in an error.
WITH UNCONDITIONAL ARRAY WRAPPER
The result is enclosed in square brackets to create a JSON array.
WITH CONDITIONAL ARRAY WRAPPER
The result is enclosed in square brackets to create a JSON array when more than one SQL/JSON element is returned or when a single SQL/JSON element that is not a JSON array or JSON object is returned.
The following table shows how each of these options is applied to the JSON text {a:"10", b:[1, 2]}.
Table 1. Results using each WRAPPER clause
WRAPPER clause path value of $.a path value of $.b
WITHOUT ARRAY WRAPPER "10" [1,2]
WITH UNCONDITIONAL ARRAY WRAPPER ["10"] [[1,2]]
WITH CONDITIONAL ARRAY WRAPPER ["10"] [1,2]
KEEP QUOTES or OMIT QUOTES
Specifies whether the surrounding quotes should be removed when a scalar string is returned.
KEEP QUOTES
Indicates quotes are not removed from scalar strings. This is the default.
OMIT QUOTES
Indicates quotes are removed from scalar strings. When OMIT QUOTES is specified, the WITH ARRAY WRAPPER clause cannot be specified
ON EMPTY
Specifies the behavior when an empty sequence is returned using sql-json-path-expression.
NULL ON EMPTY
A null value is returned. This 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 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.

Example

  • Return the JSON object associated with the name key from a 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"}
End of change