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 from the employees table:

--level 2 element: get from the customers table
select parse_json(text) as sales_person_name
from customers

Get only customers related to a particular sales person:

select *
from customers
where parse_json(text)'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) as customer_name_key
from customers

Get JSON keys as columns populated with key values:

  parse_json(text) as customer_name, -- level 2 element having one value
  parse_json(text):customer.address as customer_address,
  parse_json(text) as customer_phone,
  parse_json(text):dealership as dealership, -- level 1 element having one value
  parse_json(text) as sales_person_id,
  parse_json(text) 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
database icon
Real-time SQL collaboration is here
Get started with PopSQL and Snowflake in minutes