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 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 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 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}

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

The query below 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 significant differences in quotation marks.

SELECT variant1, variant2, variant1 = variant2 FROM jdemo2;
+--------------+-----------------+---------------------+
| VARIANT1     | VARIANT2        | VARIANT1 = VARIANT2 |
|--------------+-----------------+---------------------|
| {            | "{\"PI\":3.14}" | False               |
|   "PI": 3.14 |                 |                     |
| }            |                 |                     |
+--------------+-----------------+---------------------+
⇧
Back to top