Start of change

JSON_VALUE

The JSON_VALUE function returns an SQL scalar value from a JSON text by using an SQL/JSON path expression.

Read syntax diagramSkip visual syntax diagramJSON_VALUE( JSON-expressionFORMAT JSONFORMAT BSON ,sql-json-path-expression ASpath-name RETURNINGCLOB (2G) CCSID 1208RETURNINGdata-type NULL ON EMPTYERRORDEFAULTdefault-expressionON EMPTY NULL ON ERRORERRORDEFAULTdefault-expressionON ERROR )
data-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(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-clauseDATETIME(0)TIMESTAMP(6)(integer)
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.
Start of changeIf JSON-expression is a JSON array, it will be implicitly wrapped using a key name of SYSIBM_ROOT_ARRAY modifying the JSON-expression to be a JSON object. sql-json-path-expression is implicitly modified to account for this additional key.End of change
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 data type of the result. For CHAR and VARCHAR results, the CCSID cannot be 65535. Additional rules for result data type conversions are described here: Table 1
The default is CLOB(2G) CCSID 1208.
ON EMPTY
Specifies the behavior when an empty sequence is found using sql-json-path-expression.
NULL ON EMPTY
A null value is returned. This is the default.
ERROR ON EMPTY
An error is returned.
DEFAULT default-expression ON EMPTY
The value specified by default-expression is returned. The expression must be assignment compatible to the result data type.
ON ERROR
Specifies the behavior when an error is encountered by JSON_VALUE.
NULL ON ERROR
A null value is returned. This is the default.
ERROR ON ERROR
An error is returned.
DEFAULT default-expression ON ERROR
The value specified by default-expression is returned. The expression must be assignment compatible to the result data type.

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

Example

  • Return a value from a JSON text as an integer.
    VALUES (JSON_VALUE('{"id":"987"}', '$.id' RETURNING INTEGER));
    The result is 987.
  • Try to return a value from a JSON text that is an array value. Handle any error by returning a default string.
    VALUES (JSON_VALUE('{"friends":["John","Lisa"]}', 
                   'strict $.friends' DEFAULT 'Not found' ON ERROR));
    The result is Not found since the value corresponding to the friends key is an array, not a scalar value.
End of change