DB2 Scalar functions - JSON_VALUE


Volta a página anterior

Volta ao Menu das scalar functions

Volta ao Menu Principal


Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

JSON_VALUE

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

JSON_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
SMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(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.

© Copyright IBM Corp.