Explained: PARSE_JSON() function in Snowflake?

Are you looking to find how to use the PARSE_JSON() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the PARSE_JSON function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use PARSE_JSON() function. I will explain the PARSE_JSON() function by taking a practical example. So don’t waste time let’s start a step-by-step guide to understanding what is the PARSE_JSON() expression.

What is PARSE_JSON() Function?

PARSE_JSON() function helps to interpret a given string as a JSON document.

How can we use PARSE_JSON() Function ?

PARSE_JSON() function is used to interpret an input string as a JSON document, producing a Variant value.

  • The function supports an input expression with a maximum size of 8MB compressed.
  • If the input is NULL then the output is also NULL.
  • In JSON, an object is an unordered set of key-value pairs.
  • TO_JSON and PARSE_JSON are converse or reciprocal functions.
    • PARSE_JSON function takes a string as input and returns a JSON-compatible variant.
    • TO_JSON function takes JSON-compatible variant and returns string.

What is the Syntax of PARSE_JSON function in Snowflake?

-- Syntax : 

PARSE_JSON ( Expression )

PARSE_JSON() Argument Details :

ExpressionString value ( Mandatory )

Expression :

A String expression ( Varchar ) that holds valid JSON information.

Returns :

The data type of the returned value is Variant and contains a JSON document.

Examples of PARSE_JSON() function in Snowflake:

What is the difference between TO_JSON and PARSE_JSON for example?

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

How to use PARSE_JSON function within TO_JSON with example?

select
    to_json(parse_json('{"b":3,"a":2}')),
    to_json(parse_json('{"b":3,"a":2}')) = '{"b":3,"a":2}',
    to_json(parse_json('{"b":3,"a":2}')) = '{"a":2,"b":3}'
    ;

+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| TO_JSON(PARSE_JSON('{"B":1,"A":2}')) | TO_JSON(PARSE_JSON('{"B":3,"A":2}')) = '{"B":3,"A":2}' | TO_JSON(PARSE_JSON('{"B":3,"A":2}')) = '{"A":2,"B":3}' |
|--------------------------------------+--------------------------------------------------------+--------------------------------------------------------|
| {"a":2,"b":1}                        | False                                                  | True                                                   |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+

Snowflake official documentation for parse_json function link

Final Thoughts

In this article, we have learned about PARSE_JSON() function and uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Leave a Comment