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.

Read syntax diagramSkip visual syntax diagram JSON_VAL ( json-value , search-string , return-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:
Table 1. Data type specification for the result-type value.
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":
    SELECT JSON_VAL(EMP_DATA,'lastname','s:20')
      FROM JSON_EMP
    WHERE
      JSON_VAL(EMP_DATA,'empno','s:6') = '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 element (.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:
    "pay":{
           "salary":64680.00,
           "bonus":500.00,
           "comm":1974.00
          }
    To retrieve these three fields, you need to explicitly name them since retrieving pay alone does not work.
    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
    In the JSON_EMP table, there are a few employees who do not have middle names. The following query will return a value or 1, 0, or NULL depending on whether the middle name exists for a record.
    SELECT JSON_VAL(EMP_DATA,'lastname','s:30'),
           JSON_VAL(EMP_DATA,'midinit','u')
    FROM JSON_EMP;