JSON_TABLE

The JSON_TABLE function returns a table with two columns. First column is the BSON type and second column is the string value.

The schema is SYSTOOLS.

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 result of the function is a table with the format that is shown in the following table. All the columns are nullable.

Table 1. Table that is returned by the function.
Column name	Data type	Contains
TYPE	INTEGER	The type of the element.
VALUE	VARCHAR(2048)	The value of the element.

JSON_TABLE returns two columns: Type and Value. The type is one of a possible 18 BSON type that was described earlier. The Value is the actual contents of the field. By default, only the first number of an array is returned when you use JSON_VAL. However, there are situations where you do want to return all the values in an array. In those situations, you must use the JSON_TABLE function.

The format of the JSON_TABLE function is like JSON_VAL except that it returns a table of values. You must use this function as part of FROM clause and a table function specification.

Example

To return the contents of the phone extension array for just one employee (000230), you can use the following JSON_TABLE function:

SELECT PHONES.* 
   FROM JSON_EMP E, 
        TABLE( JSON_TABLE(E.EMP_DATA,'phoneno','i') ) AS PHONES 
WHERE JSON_VAL(E.EMP_DATA,'empno','s:6') = '000230';

The TABLE(... ) specification in the FROM clause is used for table functions. The results that are returned from the TABLE function are treated the same as a traditional table.

To create a query that gives the name of every employee and their extensions, enter the following:

SELECT JSON_VAL(E.EMP_DATA, 'lastname', 's:10') AS LASTNAME, 
       PHONES.VALUE AS PHONE 
  FROM JSON_EMP E,
       TABLE( JSON_TABLE(E.EMP_DATA,'phoneno','i') ) AS PHONES;