- Categories:
Conversion functions ,Semi-structured and structured data functions (Cast)
TO_JSON¶
Converts aVARIANT value to a string containing the JSON representation of the value.
Syntax¶
TO_JSON(<expr>)
Arguments¶
exprAn expression of type VARIANT that holds valid JSON information.
Returns¶
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 containsJSON 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 anunordered set of key-value pairs. When TO_JSON produces astring, 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-compatibleVARIANT.
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 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}
Examples¶
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 inserta VARIANT value in thev column of the table.
CREATEORREPLACETABLEjdemo1(vVARIANT);INSERTINTOjdemo1SELECTPARSE_JSON('{"food":"bard"}');
Query the data and use the TO_JSON function to convert the VARIANT value to a string.
SELECTv,v:food,TO_JSON(v)FROMjdemo1;
+------------------+--------+-----------------+| 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:
SELECTTO_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 |+---------------+--------------------------+------------------+--------------------+
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 insertsa VARCHAR value, and the UPDATE statement generates a JSON value that corresponds with that VARCHAR value.
CREATEORREPLACETABLEjdemo2(varchar1VARCHAR,variant1VARIANT);INSERTINTOjdemo2(varchar1)VALUES('{"PI":3.14}');UPDATEjdemo2SETvariant1=PARSE_JSON(varchar1);
This query shows that TO_JSON and PARSE_JSON are conceptually reciprocal functions:
SELECTvarchar1,PARSE_JSON(varchar1),variant1,TO_JSON(variant1),PARSE_JSON(varchar1)=variant1,TO_JSON(variant1)=varchar1FROMjdemo2;
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+| 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-valuepairs can prevent the output from matching the input. For example:
SELECTTO_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 |+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+