JSON_QUERY
The JSON_QUERY function returns an SQL/JSON value from the specified JSON text by using an SQL/JSON path expression.
- 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.
- 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. If more than one value has the same key, one of the JSON values will be selected.
- 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.
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.
The result is the following string that represents a JSON object.VALUES JSON_QUERY('{"id":"701", "name":{"first":"John", "last":"Doe"}}', '$.name');
{"first":"John", "last":"Doe"}