How to Query a JSON Object in Snowflake using parse_json Function
Snowflake supports querying JSON columns. This gives the advantage of storing and querying unstructured data. Here's how you can query a JSON column in Snowflake.
Get only salesperson.name
from the employees table:
--level 2 element: get salesperson.name from the customers table
select parse_json(text):salesperson.name as sales_person_name
from customers
Get only customers related to a particular sales person:
select *
from customers
where parse_json(text):salesperson.name='Alice Miller'
Get first key and its value of a JSON data set:
select top 1 parse_json(text):customer as customer_key
from customers
--going deeper to get only customer name
select top 1 parse_json(text):customer.name as customer_name_key
from customers
Get JSON keys as columns populated with key values:
select
parse_json(text):customer.name as customer_name, -- level 2 element having one value
parse_json(text):customer.address as customer_address,
parse_json(text):customer.phone as customer_phone,
parse_json(text):dealership as dealership, -- level 1 element having one value
parse_json(text):salesperson.id as sales_person_id,
parse_json(text):salesperson.name as sales_person_name,
parse_json(text):vehicle.extras[0] as extras_1, -- first value of level 2 element having three values
parse_json(text):vehicle.extras[1] as extras_2, -- second value of level 2 element having three values
parse_json(text):vehicle.extras[2] as extras_3 -- third value of level 2 element having three values
from customers
Previous
How to Upload CSVFrom Snowflake query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and Snowflake