Categories:

# 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 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 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 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 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);
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 and PARSE_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 and TO_VARIANT can take a string and return a variant, they are not equivalent. The following code uses PARSE_JSON to update one column and TO_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 |                 |                     |
| }            |                 |                     |
+--------------+-----------------+---------------------+