How to Query Json Data in Snowflake

We’ve already showed you how to create a variant column in a Snowflake table, where variant means JSON. In this tutorial, we show how to query those JSON columns.

(This article is part of our Snowflake Guide. Use the right-hand menu to navigate.)

Create a table with a JSON column

First create a database or use the inventory one we created in the last post and then create a table with one column of type variant:

use database inventory;
create table jsonRecord(jsonRecord variant);

Add JSON data to Snowflake

Then, add some data. We will add simple JSON, nested JSON, and JSON arrays (i.e. JSON objects inside brackets []) to show how to query each type. Notice the parse_json() function.

INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON('{"customer": "Walker"}');
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON('{"customer": "Stephen"}');
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON('{"customer": "Aphrodite", "age": 32}');

These records include a JSON array, orders.

i
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON(' {
            "customer": "Aphrodite",
            "age": 32,
            "orders": [{
                                    "product": "socks",
                                    "quantity": 4
                        },
                        {
                                    "product": "shoes",
                                    "quantity": 3
                        }
            ]
 }');
INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON(' {
            "customer": "Nina",
            "age": 52,
            "orders": [{
                                    "product": "socks",
                                    "quantity": 3
                        },
                        {
                                    "product": "shirt",
                                    "quantity": 2
                        }
            ]
 }');

This record includes nested JSON, meaning an attribute, address, whose value is another JSON object.

INSERT INTO JSONRECORD (jsonrecord) select PARSE_JSON(' {
            "customer": "Maria",
            "age": 22,
     "address" : { "city": "Paphos", "country": "Cyprus"},                                                   
            "orders": [{
                                    "product": "socks",
                                    "quantity": 3
                        },
                        {
                                    "product": "shirt",
                                    "quantity": 2
                        }
            ]
 }');

Now key select * from JSONRECORD to show all the records. Note that these are case-sensitive:

• Function
• Column
• Table names