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