- Categories:
Conversion Functions , Semi-structured Data Functions (Cast)
TO_JSON¶
Converts a 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.
Returns¶
The returned value is of type VARCHAR
.
Usage Notes¶
If the input is NULL, the output is also NULL. If the input is a VARIANT that contains JSON null, then the returned value is the string
'"null"'
(i.e. the word “null” surrounded by double quotes). An example is included in the Examples section below.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.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(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 because:
Empty strings, and strings with only whitespace, are not handled reciprocally. For example, the return value of
PARSE_JSON('')
is NULL, but the return value ofTO_JSON(NULL)
is not''
.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
.
For example, 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; +------------------+--------+-----------------+ | V | V:FOOD | TO_JSON(V) | |------------------+--------+-----------------| | { | "bard" | {"food":"bard"} | | "food": "bard" | | | | } | | | +------------------+--------+-----------------+
The following example shows NULL handling for PARSE_JSON
and TO_JSON
:
SELECT TO_JSON(NULL), TO_JSON('null'::VARIANT), PARSE_JSON(NULL), PARSE_JSON('null'); +---------------+--------------------------+------------------+--------------------+ | TO_JSON(NULL) | TO_JSON('NULL'::VARIANT) | PARSE_JSON(NULL) | PARSE_JSON('NULL') | |---------------+--------------------------+------------------+--------------------| | NULL | "null" | NULL | null | +---------------+--------------------------+------------------+--------------------+
The following examples demonstrate the relationship among PARSE_JSON
, TO_JSON
, and TO_VARIANT
:
Create a table and add VARCHAR, generic VARIANT, and JSON-compatible VARIANT data. The INSERT statement inserts a VARCHAR value, and the UPDATE statement generates a JSON value that corresponds to that VARCHAR.
CREATE or replace TABLE jdemo2 (varchar1 VARCHAR, variant1 VARIANT, variant2 VARIANT); INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}'); UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);This query shows that
TO_JSON
andPARSE_JSON
are conceptually reciprocal functions:SELECT varchar1, PARSE_JSON(varchar1), variant1, TO_JSON(variant1), PARSE_JSON(varchar1) = variant1, TO_JSON(variant1) = varchar1 FROM jdemo2; +-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+ | 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 | | | | | | } | } | | | | +-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+However, the functions are not exactly reciprocal; differences in whitespace or order of key-value pairs can prevent the output from matching the input. For example:
SELECT TO_JSON(PARSE_JSON('{"b":1,"a":2}')), TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"b":1,"a":2}', TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"a":2,"b":1}' ; +--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+ | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"B":1,"A":2}' | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"A":2,"B":1}' | |--------------------------------------+--------------------------------------------------------+--------------------------------------------------------| | {"a":2,"b":1} | False | True | +--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+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 columnvariant1
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);The query below shows that the output of
PARSE_JSON
and the output ofTO_VARIANT
are not the same. In addition to the trivial difference(s) in whitespace, there are significant differences in quotation marks.SELECT variant1, variant2, variant1 = variant2 FROM jdemo2; +--------------+-----------------+---------------------+ | VARIANT1 | VARIANT2 | VARIANT1 = VARIANT2 | |--------------+-----------------+---------------------| | { | "{\"PI\":3.14}" | False | | "PI": 3.14 | | | | } | | | +--------------+-----------------+---------------------+