- Categories:
Semi-structured Data Functions (Parsing)
PARSE_JSON¶
Interprets an input string as a JSON document, producing a VARIANT value.
- See also:
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 8 MB 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 containingnull
.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 (e.g., 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.
In JSON, an object (also called a “dictionary” or a “hash”) is an unordered set of key-value pairs.
TO_JSON
andPARSE_JSON
are (almost) converse or reciprocal functions.The
PARSE_JSON
function takes a string as input and returns a JSON-compatible variant. TheTO_JSON
function takes a JSON-compatible variant and returns a string. The following is (conceptually) true if X is a string containing valid JSON:X = TO_JSON(PARSE_JSON(X));
For example, the following is (conceptually) true:
'{"pi":3.14,"e":2.71}' = TO_JSON(PARSE_JSON('{"pi":3.14,"e":2.71}'))
However, the functions are not perfectly reciprocal for two reasons:
The order of the key-value pairs in the string produced by
TO_JSON
is not predictable.The string produced by
TO_JSON
can have less whitespace than the string passed toPARSE_JSON
.
The following are equivalent JSON, but not equivalent strings:
{"pi": 3.14, "e": 2.71}
{"e":2.71,"pi":3.14}
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 thePARSE_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 | | | | } | | +---+------------------------+------------+