JSON_VAL
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
- A BLOB column of a base table. The json-value value must contain a BSON representation of a JSON document.
- search-string
- A path-qualified JSON field name.
- return-type
- The return type of data that is returned.
The search-string and return-type must be constants, they cannot be variables. Therefore, their use in user-defined functions is limited to constants.
A typical JSON record contains various data types and structures as illustrated by the following
record:
{
"empno":"200170",
"firstnme":"KIYOSHI",
"midinit":"",
"lastname":"YAMAMOTO",
"workdept":"D11",
"phoneno":[2890],
"hiredate":"09/15/2005",
"job":"DESIGNER",
"edlevel":16,
"sex":"M",
"birthdate":"01/05/1981",
"pay":{
"salary":64680.00,
"bonus":500.00,
"comm":1974.00
}
}
There
are numbers of fields with different formats, including strings (firstnme
),
integers (edlevel
), decimal (salary
), date
(hiredate
), a number array (phoneno
), and a structure
(pay
). JSON data can consist of nested objects, arrays, and complex structures. The
format of a JSON object is checked when you use the JSON2BSON function and an error message is
issued if it does not conform to the JSON specification.The JSON_VAL function needs to know how to return the data type back from the JSON record, so you
need to specify the format. The possible formats are:
Return Type | Format |
---|---|
n | DECFLOAT |
i | INTEGER |
l | BIGINT (notice a lowercase L) |
f | DOUBLE |
d | DATE |
ts | TIMESTAMP(6) |
t | TIME |
s:n | VARCHAR(n), where n is an integer constant 1 - 32672 |
b:n | VARBINARY(n), where n is an integer constant 1 - 32672 |
u | Null flag (integer) with a value of 0 or 1 |
Examples
- Example 1: The following example retrieves the last name of the employee whose employee
number is
"200170":
Selecting data from an array type gives you the first value (element zero). If you need to access a specific array element in a field, you can use the dot notation after the field name. The first element starts at zero. If you select the second elementSELECT JSON_VAL(EMP_DATA,'lastname','s:20') FROM JSON_EMP WHERE JSON_VAL(EMP_DATA,'empno','s:6') = '200170';
(.1)
, all the records that have a second element will have its value retrieved while the ones that don't will have a null value. -
Example 2: Structured fields are retrieved by using the same dot notation as arrays. The field is specified by using the
field.subfield
format and these fields can be an arbitrary number of levels deep. The pay field in the employee record is made up of three more fields:
To retrieve these three fields, you need to explicitly name them since retrieving pay alone does not work."pay":{ "salary":64680.00, "bonus":500.00, "comm":1974.00 }
SELECT JSON_VAL(EMP_DATA,'pay.salary','i'), JSON_VAL(EMP_DATA,'pay.bonus','i'), JSON_VAL(EMP_DATA,'pay.comm','i') FROM JSON_EMP WHERE JSON_VAL(EMP_DATA,'empno','s:6') = '200170';
The "u" flag is used to determine if a field has any contents in it. If you use the "u" flag, the value returned will be either:- 1 - The field exists, and it has a value (not null or empty string)
- 0 - The field exists, but the value is null or empty
- null - The field does not exist
SELECT JSON_VAL(EMP_DATA,'lastname','s:30'), JSON_VAL(EMP_DATA,'midinit','u') FROM JSON_EMP;