How to Query JSON in Snowflake

How do I parse and query JSON in Snowflake?

Snowflake’s JSON support lets you store, parse, and query semi-structured data with the VARIANT type, PARSE_JSON, dot notation, and FLATTEN.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does Snowflake JSON solve?

JSON handling allows you to keep semi-structured data in its raw form while still filtering, joining, and aggregating it with regular SQL. No complex ETL is needed.

How to store raw JSON?

Use the VARIANT column type

Create tables with a VARIANT column. Insert raw JSON strings directly; Snowflake auto-casts them to VARIANT.

How to parse JSON strings?

PARSE_JSON() converts VARCHAR → VARIANT

Wrap incoming text with PARSE_JSON() when Snowflake cannot infer the type automatically or when loading from staged files.

How to access JSON fields?

Dot & bracket notation

Use : and [] to traverse objects and arrays. Cast the result to the desired scalar type for filtering or math.

How to explode JSON arrays?

FLATTEN() with LATERAL

FLATTEN iterates array elements and key–value pairs. Join its output to the base table with , LATERAL or CROSS JOIN.

Best practices for JSON performance

Store only needed keys, add clustered keys on frequently filtered elements, and avoid SELECT * on large VARIANT columns.

Real-world ecommerce example

The example below parses an order_meta JSON column, joins it to Orders, and explodes a items array into rows for reporting.

Why How to Query JSON in Snowflake is important

How to Query JSON in Snowflake Example Usage


SELECT c.name,
       o.id                           AS order_id,
       f.value:sku::INT               AS product_id,
       p.name                         AS product_name,
       f.value:qty::INT               AS quantity,
       f.value:qty::INT * p.price     AS line_total
FROM Orders o
JOIN Customers c   ON c.id = o.customer_id
JOIN LATERAL FLATTEN(input => o.order_meta:items) f ON TRUE
JOIN Products  p   ON p.id = f.value:sku::INT
WHERE o.order_date >= '2024-01-01';

How to Query JSON in Snowflake Syntax


-- Create a table with JSON
aCREATE TABLE Orders (
    id            INT,
    customer_id   INT,
    order_date    DATE,
    total_amount  NUMERIC(12,2),
    order_meta    VARIANT -- raw JSON
);

-- Insert raw JSON directly
INSERT INTO Orders
VALUES (1, 10, '2024-03-01', 120.00, PARSE_JSON('{"shipping":{"city":"Austin","state":"TX"}, "items":[{"sku":101,"qty":2},{"sku":202,"qty":1}]}'));

-- Access scalar
SELECT order_meta:shipping.city::STRING AS city
FROM Orders;

-- Flatten array
SELECT o.id,
       f.value:sku::INT    AS product_id,
       f.value:qty::INT    AS quantity
FROM Orders o,
     LATERAL FLATTEN(input => o.order_meta:items) f;

Common Mistakes

Skipping the VARIANT cast. Inserting plain VARCHAR JSON without PARSE_JSON() forces every query to parse on the fly—slowing scans. Always load as VARIANT.
Forgetting LATERAL with FLATTEN. Omitting LATERAL produces one row with a nested ARRAY, not one row per element. Always join FLATTEN with LATERAL or CROSS JOIN.