Converts a VARIANT value to a string containing the JSON representation of the value.
Syntax¶ Arguments¶expr
An expression of type VARIANT that holds valid JSON information.
Returns a value of type VARCHAR.
If the input is NULL, the function returns NULL.
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). See the example 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 and PARSE_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 of TO_JSON(NULL) is NULL, not the reciprocal ''.
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 PARSE_JSON.
For example, the following are equivalent JSON, but not equivalent strings:
{"pi": 3.14, "e": 2.71}
{"e":2.71,"pi":3.14}
The following examples use the TO_JSON function.
Inserting VARIANT values and converting them to strings with a query¶Create and fill a table. The INSERT statement uses the PARSE_JSON function to insert a VARIANT value in the v column of the table.
CREATE OR REPLACE TABLE jdemo1 (v VARIANT);
INSERT INTO jdemo1 SELECT PARSE_JSON('{"food":"bard"}');
Copy
Query the data and use the TO_JSON function to convert the VARIANT value to a string.
SELECT v, v:food, TO_JSON(v) FROM jdemo1;
Copy
+------------------+--------+-----------------+
| V | V:FOOD | TO_JSON(V) |
|------------------+--------+-----------------|
| { | "bard" | {"food":"bard"} |
| "food": "bard" | | |
| } | | |
+------------------+--------+-----------------+
Handling NULL values with the PARSE_JSON and TO_JSON functions¶
The following example shows how PARSE_JSON and TO_JSON handle NULL values:
SELECT TO_JSON(NULL), TO_JSON('null'::VARIANT),
PARSE_JSON(NULL), PARSE_JSON('null');
Copy
+---------------+--------------------------+------------------+--------------------+
| TO_JSON(NULL) | TO_JSON('NULL'::VARIANT) | PARSE_JSON(NULL) | PARSE_JSON('NULL') |
|---------------+--------------------------+------------------+--------------------|
| NULL | "null" | NULL | null |
+---------------+--------------------------+------------------+--------------------+
Comparing PARSE_JSON and TO_JSON¶
The following examples demonstrate the relationship between the PARSE_JSON and TO_JSON functions.
This example creates a table with a VARCHAR column and a VARIANT column. The INSERT statement inserts a VARCHAR value, and the UPDATE statement generates a JSON value that corresponds with that VARCHAR value.
CREATE OR REPLACE TABLE jdemo2 (
varchar1 VARCHAR,
variant1 VARIANT);
INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}');
UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);
Copy
This query shows that TO_JSON and PARSE_JSON are conceptually reciprocal functions:
SELECT varchar1,
PARSE_JSON(varchar1),
variant1,
TO_JSON(variant1),
PARSE_JSON(varchar1) = variant1,
TO_JSON(variant1) = varchar1
FROM jdemo2;
Copy
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+
| 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 in the 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}';
Copy
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| 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 |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4