JSON_VAL scalar function

The JSON_VAL function provides an SQL interface to extract and retrieve JSON data into SQL data types from BSON objects. The JSON_VAL function returns an element of a JSON document that is identified by the JSON field name that is specified in search-string. The value of the JSON element is returned in the data type and length that is specified in result-type.

The schema is SYSIBM.

json-value

An expression that returns a BLOB value. The json-value must contain a BSON representation of a JSON document.

search-string

A character or graphic string constant that is not longer than 2048 bytes. search-string contains the path-qualified JSON field name. If the value is not CCSID 1208, the value is converted to CCSID 1208 before the function is evaluated.

result-type

A character string constant that is not longer than 32 bytes that specifies the characteristics for the result of the function. The constant is not case-sensitive. The string ':na' can be appended to the data type to indicate that json-value must not contain a JSON array. Leading blanks can be specified before ':' and trailing blanks can be specified after ':'. For example, ' s:40', 's :40', 'b: 10', 'ts: na ', 's:40 :na', and 's:40: na ' can be specified. The result-type value must contain a data type specification from the following table.

Table 1. Supported constant values for the data type portion of `result-type`.
`result-type`	SQL data type of the result
n	DECFLOAT(34)
i	INTEGER
l	BIGINT
f	DOUBLE
d	DATE
ts	TIMESTAMP(6)
t	TIME
s:`n`	VARCHAR(`n`), where `n` is an integer constant 1–32672. The CCSID of the result is the CCSID for Unicode mixed data (1208).
b:`n`	VARCHAR(`n`) FOR BIT DATA, where `n` is an integer constant 1–32672.
u	INTEGER, with values 0 or 1.

The JSON field name can identify a scalar value or a JSON array value. If ':na' is specified as part of result-type, the result must not be an array. If ':na' is not specified in result-type and the result is an array, the value of the first element of the identified array is returned.

The result of the JSON_VAL function is the data type and length that is specified by result-type. The result can be null.

The result is determined by the following criteria:

If the JSON field name is not found in json-value, the result is the null value.
If the BSON_TYPE of the JSON field in json-value is BSON_DBREF or BSON_UNDEFINED, the result is the null value.
If result-type is 'u', the result value describes the value of the specified JSON field in json-value. The result depends on the following values of the JSON field:

1

A value exists and the value is not the null value or an empty string. For example: {name:"Joe"}.

0

A value exists and the value is the null value. For example: {name:null}.

null

A value does not exist. For example: { }.
Otherwise, if necessary, the value of the specified JSON field in json-value is converted to result-type. If the value of the JSON field cannot be converted to result-type, the null value is returned. For example, assume that the value of the JSON field is an arbitrary string that cannot be converted to an integer, and result-type is 'i'. The null value is returned. If the length of the data is longer than the length specified by result-type, the result is the null value. Also, if the result is VARCHAR or a datetime value (DATE, TIME, or TIMESTAMP), the encoding scheme is Unicode.

Notes

Assignment of arguments to parameters

When the JSON_VAL function is invoked and arguments are assigned to the parameter types of the function, or the result of the function is assigned to the target on return from the function, implicit cast is not used.

Statement concentrator

Constant values that are specified as arguments cannot be replaced with parameter markers for use with the statement concentrator.

CREATE INDEX statement considerations

The following considerations apply to CREATE INDEX statements that invoke the JSON_VAL function:

If key-expression invokes the JSON_VAL function and the first argument is a LOB column, the column must be defined as an inline LOB.
If key-expression invokes the JSON_VAL function, the function invocation must meet the following conditions:
- The invocation of the JSON_VAL function must be the outermost expression for key-expression.
- If the first argument is a column, that column must be contained in a table in a partition-by-growth table space.
- The third argument must end with the string ':na', to indicate that the first argument does not contain a JSON array.
If key-expression invokes the JSON_VAL built-in function, the CREATE INDEX statement must not reference any LOB columns other than the LOB column that is the argument to the JSON_VAL function. Such a CREATE INDEX statement can refer only to a single LOB column.

Date value restrictions

When $DATE is used in the JSON document, only date values from 01 January 1970 through 31 December 2037 are supported. End of change

Examples

Assume that the DATA column in table 'table1' contains a row with the following JSON document:

{
	name:"Joe",
	age:5,
	phone:["555-666-7777", "444-789-1234"],
	address:{
		street:"ABC st",
		zipcode:"95141"
               }
}

You can use the JSON_VAL function to find addresses with a zip code of '95141'.

SELECT * FROM "table1" 
   WHERE JSON_VAL(DATA, 'address.zipcode', ‘s:5’)='95141';

The following examples show how you can use the search-string argument. Assume that the table 'json_table' contains four rows with the following JSON objects. Each JSON object is stored as BSON in a BLOB column named DATA.
```
{
     name: "Joe",
      isMarried:true,
      phone: 82113456,
      partner:
      {
        name: "Kate",
         isMarried:true, 
         phone: 82113111,
         son: 
         {
            name: "Lock",
             isMarried:false, 
             phone: 81231232, 81231233
}
}
}

{
     name:"Mary"
      isMarried:-1,
      phone: “82111432”
      partner: “NULL”
}

{
     name:"Henry",
      isMarried:0,
      phone: “NULL”
}

{
     name:"Bill",
      isMarried:false
} 
```
Extract the name of the object:
You can use the JSON_VAL function to extract the name of the object whose partner's son's name (search-string of 'partner.son.name') is 'Lock' and return the value as VARCHAR(40).
```
SELECT JSON_VAL( data, 'name' , 's:40' ) FROM json_table 
     WHERE JSON_VAL( data, 'partner.son.name' , 's:40' ) = 'Lock';
```
The result is 'Joe'.
Extract the name of the partner's partner:
You can use the JSON_VAL function to extract the name of the partner's partner (search-string of 'partner.partner') for each row and return the value as VARCHAR(40).
```
SELECT JSON_VAL( data, 'partner.partner ' , 's:40' ) FROM json_table ;
```
This example returns four rows, each containing the null value because the search string 'partner.partner' does not exist for the objects in any row.