How to read hierarchical JSON array data in Snowflake?

In this blog, you will learn how to read hierarchical JSON array data in Snowflake.

To read hierarchical JSON array data in Snowflake, you will need to use Snowflake’s JSON parsing functions to extract and query the data.

Let’s go through an example using a dataset with JSON array data, create a table, insert the data, and fetch it.

Sample data set:

[
{
"name": "John Doe",
"age": 30,
"address": {
"city": "New York",
"zip": "10001"
},
"languages": ["English", "Spanish", "French"]
},
{
"name": "Jane Smith",
"age": 28,
"address": {
"city": "Los Angeles",
"zip": "90001"
},
"languages": ["English", "German"]
},
{
"name": "Alice Johnson",
"age": 35,
"address": {
"city": "Chicago",
"zip": "60601"
},
"languages": ["English", "Mandarin"]
},
{
"name": "Bob Brown",
"age": 32,
"address": {
"city": "San Francisco",
"zip": "94101"
},
"languages": ["English"]
},
{
"name": "Eva Lee",
"age": 25,
"address": {
"city": "Miami",
"zip": "33101"
},
"languages": ["Spanish"]
}
]

Create a Table

Create a table with a VARIANT column data type to store the JSON array data, you can create either a permanent or temporary table.

CREATE OR REPLACE TEMPORARY TABLE my_temp_table (
json_data VARIANT
);

Insert JSON array data

Insert the JSON array data into the table:

INSERT INTO my_temp_table (json_data)
SELECT PARSE_JSON('
[
{
"name": "John Doe",
"age": 30,
"address": {
"city": "New York",
"zip": "10001"
},
"languages": ["English", "Spanish", "French"]
},
{
"name": "Jane Smith",
"age": 28,
"address": {
"city": "Los Angeles",
"zip": "90001"
},
"languages": ["English", "German"]
},
{
"name": "Alice Johnson",
"age": 35,
"address": {
"city": "Chicago",
"zip": "60601"
},
"languages": ["English", "Mandarin"]
},
{
"name": "Bob Brown",
"age": 32,
"address": {
"city": "San Francisco",
"zip": "94101"
},
"languages": ["English"]
},
{
"name": "Eva Lee",
"age": 25,
"address": {
"city": "Miami",
"zip": "33101"
},
"languages": ["Spanish"]
}
]

') AS json_data;

Fetch JSON Array Data

Select * from my_temp_table;

Retrieve each element in the JSON array as separate rows

SELECT
f.value:name::string AS name,
f.value:age::integer AS age,
f.value:address:city::string AS city,
f.value:address:zip::string AS zip,
f.value:languages[0]::string AS language1,
f.value:languages[1]::string AS language2,
f.value:languages[2]::string AS language3
FROM my_temp_table,
LATERAL FLATTEN(input => json_data) AS f;

The LATERAL FLATTEN function to transform the JSON array into rows.

The individual elements within each row using Snowflake’s JSON notation, such as, f.value:name::string to extract the name.

Thank you for reading this post, and I hope you enjoyed it!