- Categories:
Conversion Functions , Semi-structured Data Functions (Cast)
TO_JSON¶
Converts any VARIANT value to a string containing the JSON representation of the value. If the input is NULL, the result is also NULL.
Syntax¶
TO_JSON( <expr> )
Arguments¶
expr
An expression of type VARIANT that holds valid JSON information.
Usage Notes¶
A JSON object (also called a “dictionary” or a “hash”) is an unordered set of key-value pairs. When
TO_JSON
produces a string, the order of the key-value pairs in that string is not predictable.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 is a short example of TO_JSON
and PARSE_JSON
:
CREATE TABLE jdemo1 (v VARIANT); INSERT INTO jdemo1 SELECT PARSE_JSON('{"food":"bard"}');SELECT v, v:food, TO_JSON(v) FROM jdemo1;Output:
+------------------+--------+-----------------+ | V | V:FOOD | TO_JSON(V) | |------------------+--------+-----------------| | { | "bard" | {"food":"bard"} | | "food": "bard" | | | | } | | | +------------------+--------+-----------------+
The following example demonstrates the relationship among PARSE_JSON
,
TO_JSON
, and TO_VARIANT
:
Create a table and add VARCHAR, generic VARIANT, and JSON-compatible VARIANT data:
CREATE or replace TABLE jdemo2 (varchar1 VARCHAR, variant1 VARIANT, variant2 VARIANT); -- Add a VARCHAR value. INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}'); -- Generate a JSON value that corresponds to the VARCHAR. UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);This query shows that
TO_JSON
andPARSE_JSON
are effectively reciprocal functions (if there are no whitespace differences):SELECT varchar1, PARSE_JSON(varchar1), variant1, TO_JSON(variant1), PARSE_JSON(varchar1) = variant1, TO_JSON(variant1) = varchar1 FROM jdemo2;Output:
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+ | VARCHAR1 | PARSE_JSON(VARCHAR1) | VARIANT1 | TO_JSON(VARIANT1) | PARSE_JSON(VARCHAR1) = VARIANT1 | TO_JSON(VARIANT1) = VARCHAR1 | |-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------| | {"PI":3.14} | { | { | {"PI":3.14} | True | True | | | "PI": 3.14 | "PI": 3.14 | | | | | | } | } | | | | +-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+Although both
PARSE_JSON
andTO_VARIANT
can take a string and return a variant, they are not equivalent. The following code usesPARSE_JSON
to update one column andTO_VARIANT
to update the other column (the update to column variant1 is unnecessary because it was updated earlier using an identical function call; however, the code below updates it again so that you can see side-by-side which functions are called to update the columns):UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1), variant2 = TO_VARIANT(varchar1); SELECT variant1, variant2, variant1 = variant2 FROM jdemo2;The output shows that the output of parse_json() and the output of to_variant() are not the same. In addition to the trivial difference(s) in whitespace, there are more significant differences in quotation marks.
+--------------+-----------------+---------------------+ | VARIANT1 | VARIANT2 | VARIANT1 = VARIANT2 | |--------------+-----------------+---------------------| | { | "{\"PI\":3.14}" | False | | "PI": 3.14 | | | | } | | | +--------------+-----------------+---------------------+