JSON_VALUE scalar function

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

Read syntax diagramSkip visual syntax diagramJSON_VALUE(JSON-expression FORMAT JSONFORMAT BSON,sql-json-path-expression ASpath-nameRETURNING CLOB(2G)RETURNINGdata-type NULL ON EMPTYERRORDEFAULTdefault-expressionON EMPTY NULL ON ERRORERRORDEFAULTdefault-expressionON ERROR)
data-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)( precision-integer,0, scale-integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)CHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)BINARY VARYINGVARBINARY(integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)

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
Specifies the data type of the result. The default is CLOB (2 GB). The default encoding used when data-type is a binary string type is UTF-8.

See CREATE TABLE statement for the description of built-in data types.

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.
DEFAULT default-expression ON EMPTY
The value that is specified by default-expression is returned. The data type of default-expression must be the same as the returning data type (SQLSTATE 42815).
ON ERROR
Specifies the behavior when an error is encountered by JSON_VALUE.
NULL ON ERROR
A null value is returned. This clause is the default.
ERROR ON ERROR
An error is returned.
DEFAULT default-expression ON ERROR
The value that is specified by default-expression is returned. The data type of default-expression must be the same as the returning data type (SQLSTATE 42815).

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)

Examples

  1. Return a value from JSON text as an integer.
    VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER));

    The result is 987.

  2. Get the value for the bonus field from JSON text. Return it as an integer.
    VALUES (JSON_VALUE('{"pay":{"salary":94250.00,"bonus":800.00,"comm":3300.00}}', 
                   'strict $.pay.bonus' RETURNING INTEGER));
    The result is 800.