Machine Learning & Big Data Blog

How To Query JSON Data in Snowflake

3 minute read
Walker Rowe

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.

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

json1

How to select JSON data in Snowflake

The format for selecting data includes all of the following:

  • tableName:attribute
  • tableName.attribute.JsonKey
  • tableName.attribute.JsonKey[arrayIndex]
  • tableName.attribute[‘JsonKey’]
  • get_path(tableName, attribute)

Here we select the customer key from the JSON record. In JSON we call the items key value pairs, like: {“key”: “value”}.

select jsonrecord:customer from JSONRECORD;

look like this:

We can also use the get_path() function:

select get_path(jsonrecord, 'address') from JSONRECORD;

Here we add a where clause, using the same colon(:) and dot (.) notation as in the other side of the select statement.

select jsonrecord:address.city from JSONRECORD where jsonrecord:customer = 'Maria';

We use an alternate approach. We get nested JSON objects by putting the keys in brackets [].

select jsonrecord['address']['city'] from JSONRECORD where jsonrecord:customer = 'Maria';

Values which do not exist are shown as NULL.

Here we pick the first element from an array since the array index (It starts at 0.).

select jsonrecord['orders'][0] from JSONRECORD where jsonrecord:customer = 'Maria';

Here we use the colon (:) to get the same column.

select jsonrecord:orders[0] from JSONRECORD where jsonrecord:customer = 'Maria';

Results:

{ "product": "socks", "quantity": 3 }

Here, we flatten the array. This record has two order JSON records. So, it shows two rows in the results, with each record attached to the other attributes.

In other words, it explodes it out to array_size rows, filling out the other columns with the non-array columns in the select statement. Think of it as an easy was to show all the orders a customer made where the order data and the customer data are repeated to make it easy to see:

select jsonrecord:customer, jsonrecord:orders  from JSONRECORD ,
   lateral flatten(input => jsonrecord:orders) prod ;

BMC, Control-M support Snowflake

BMC is a member of the Snowflake Technology Alliance Partner program. Snowflake’s cloud data platform helps customers to accelerate the data-driven enterprise with Snowflake’s market-leading, built-for-cloud data warehouse and Control-M, our market-leading enterprise application workflow orchestration platform.

(Learn how to integrate Snowflake with Control-M.)

Additional resources

For more tutorials like this, explore these resources:

Learn ML with our free downloadable guide

This e-book teaches machine learning in the simplest way possible. This book is for managers, programmers, directors – and anyone else who wants to learn machine learning. We start with very basic stats and algebra and build upon that.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

BMC Bring the A-Game

From core to cloud to edge, BMC delivers the software and services that enable nearly 10,000 global customers, including 84% of the Forbes Global 100, to thrive in their ongoing evolution to an Autonomous Digital Enterprise.
Learn more about BMC ›

About the author

Walker Rowe

Walker Rowe is an American freelancer tech writer and programmer living in Cyprus. He writes tutorials on analytics and big data and specializes in documenting SDKs and APIs. He is the founder of the Hypatia Academy Cyprus, an online school to teach secondary school children programming. You can find Walker here and here.