Categories:
Semi-structured Data Functions (Parsing)

PARSE_JSON

Interprets an input string as a JSON document, producing a VARIANT value.

Syntax

PARSE_JSON( <expr> )

Arguments

expr
An expression of string type (e.g. VARCHAR) that holds valid JSON information.

Returns

The returned value is of type VARIANT and contains a JSON document.

Usage Notes

  • This function supports an input expression with a maximum size of 8MB compressed.
  • If the input is NULL, the output is also NULL. However, if the input string is 'null', it is interpreted as a JSON null value so that the result is not SQL NULL, but a valid VARIANT value containing null.
  • When parsing decimal numbers, PARSE_JSON attempts to preserve exactness of the representation by treating 123.45 as NUMBER(5,2), not as a DOUBLE. However, numbers using scientific notation (i.e. 1.2345e+02) or numbers which cannot be stored as fixed-point decimals due to range or scale limitations are stored as DOUBLE. Because JSON does not represent values such as TIMESTAMP, DATE, TIME, or BINARY natively, these have to be represented as strings.

Examples

This shows an example of storing different types of data in a VARIANT column by calling PARSE_JSON to parse strings that contain values that can be parsed as JSON:

Create and fill a table. Note that the INSERT statement uses the PARSE_JSON function.

create or replace table vartab (n number(2), v variant);

insert into vartab
    select column1 as n, parse_json(column2) as v
    from values (1, 'null'), 
                (2, null), 
                (3, 'true'),
                (4, '-17'), 
                (5, '123.12'), 
                (6, '1.912e2'),
                (7, '"Om ara pa ca na dhih"  '), 
                (8, '[-1, 12, 289, 2188, false,]'), 
                (9, '{ "x" : "abc", "y" : false, "z": 10} ') 
       AS vals;

Query the data:

select n, v, typeof(v) from vartab;
+---+------------------------+------------+
| N | V                      | TYPEOF(V)  |
|---+------------------------+------------|
| 1 | null                   | NULL_VALUE |
| 2 | NULL                   | NULL       |
| 3 | true                   | BOOLEAN    |
| 4 | -17                    | INTEGER    |
| 5 | 123.12                 | DECIMAL    |
| 6 | 1.912000000000000e+02  | DOUBLE     |
| 7 | "Om ara pa ca na dhih" | VARCHAR    |
| 8 | [                      | ARRAY      |
|   |   -1,                  |            |
|   |   12,                  |            |
|   |   289,                 |            |
|   |   2188,                |            |
|   |   false,               |            |
|   |   undefined            |            |
|   | ]                      |            |
| 9 | {                      | OBJECT     |
|   |   "x": "abc",          |            |
|   |   "y": false,          |            |
|   |   "z": 10              |            |
|   | }                      |            |
+---+------------------------+------------+