Sign In

How to Query a JSON Object in Snowflake

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
Cta
From Snowflake query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and Snowflake
Sign up for free
Previous
How to Import a CSV
Next
How to Query Date and Time