TRY_PARSE_JSON¶

Syntax¶

TRY_PARSE_JSON( <expr> )

Arguments¶

expr

An expression of string type (for example, VARCHAR).

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 containing null.

• When parsing decimal numbers, this function attempts to preserve exactness of the representation by treating 123.45 as NUMBER(5,2), not as a DOUBLE. However, numbers using scientific notation (for example, 1.2345e+02), or numbers that 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 and TRY_PARSE_JSON are (almost) converse or reciprocal functions.

  The TRY_PARSE_JSON function takes a string as input and returns a JSON-compatible variant. The TO_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(TRY_PARSE_JSON(X));

  For example, the following is (conceptually) true:

  '{"pi":3.14,"e":2.71}' = TO_JSON(TRY_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 to TRY_PARSE_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 TRY_PARSE_JSON to parse strings that contain values that can be parsed as JSON:

Create and fill a table.

CREATE OR REPLACE TEMPORARY TABLE vartab (ID INTEGER, v VARCHAR);

INSERT INTO vartab (id, v) VALUES
  (1, '[-1, 12, 289, 2188, FALSE,]'),
  (2, '{ "x" : "abc", "y" : FALSE, "z": 10} '),
  (3, '{ "bad" : "json", "missing" : TRUE, "close_brace": 10 ');

Query the data, using TRY_PARSE_JSON. Note that the value for the third line is NULL. If the query used PARSE_JSON rather than TRY_PARSE_JSON, it would fail.

SELECT ID, TRY_PARSE_JSON(v)
  FROM vartab
  ORDER BY ID;

+----+-------------------+
| ID | TRY_PARSE_JSON(V) |
|----+-------------------|
|  1 | [                 |
|    |   -1,             |
|    |   12,             |
|    |   289,            |
|    |   2188,           |
|    |   false,          |
|    |   undefined       |
|    | ]                 |
|  2 | {                 |
|    |   "x": "abc",     |
|    |   "y": false,     |
|    |   "z": 10         |
|    | }                 |
|  3 | NULL              |
+----+-------------------+