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 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);
 -- 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 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 |                 |                     |
| }            |                 |                     |
+--------------+-----------------+---------------------+