How to Query a JSON Object in Snowflake 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

Previous

Next

Ready for a modern SQL editor?