JSON_TABLE table function
The JSON_TABLE user-defined function returns a table with two columns. First column is the BSON type and second column is the string value.
The schema is SYSTOOLS.
- injson
- An expression that returns a JSON document in binary format (extended BSON).
- inelem
- A character or graphic string constant that is not longer than 2048 bytes. inelem contains the path-qualified JSON field name to be searched for.
- rettype
- A character string constant that is no longer than 32 bytes that specifies the characteristics for the result of the function. rettype must contain a data type specification, see JSON_VAL scalar function. The value is not case-sensitive.
The result of the function is a table with the format that is shown in the following table. All the columns are nullable.
| Column name | Data type | Contains |
|---|---|---|
| TYPE | INTEGER | The type of the element. |
| VALUE | VARCHAR(2048) | The value of the element. |
Notes for JSON_TABLE
- CCSID rules
-
The JSON_TABLE function follows the CCSID rules for user-defined functions, which means that it returns a string data type in the appropriate CCSID of the application encoding scheme. So, you might need to use the CAST function to avoid problems with the encoding of returned data.
Date value restrictions
- When $DATE is used in the JSON document, only date values from 01 January 1970 through 31 December 2037 are supported.
Example
Assume that table T1 contains the following three rows of a JSON document, in BSON format, in a column C1.
{person: {name: “Mike”, age: 25}}
{person: {name: “John”, age: 42}}
{person: {name: “Kevin”, age: 34}}The following query returns a table with two columns: TYPE and VALUE.
SELECT X.*
FROM T1, TABLE(SYSTOOLS.JSON_TABLE(C1,'person', 's:50')) X +----------------------------------
| TYPE | VALUE
+----------------------------------
1_| 3 | {name:"Mike",age:25}
2_| 3 | {name:"John",age:42}
3_| 3 | {name:"Kevin",age:34}
+----------------------------------TYPE contains the BSON type of element ‘person’ and VALUE contains the value of ‘person’. The TYPE 3 is the BSON type for an embedded document (nested JSON object).