Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 38 ยท Querying and Semi-structured Data

Querying JSON: Paths, Arrays and Objects

Practical Snowflake JSON querying: GET_PATH, array functions, filtering and aggregating inside VARIANT, JSON null versus SQL NULL, and building views over raw events.

You can store JSON and flatten arrays; now put it all to work on realistic questions. This lesson covers functions for navigating and filtering documents, the surprising difference between JSON null and SQL NULL, and a clean pattern for exposing raw JSON through a friendly view.

All runnable examples use SQLite's JSON functions, which are close relatives of Snowflake's. Each stand-in shows the Snowflake spelling in a comment.

A sample set of events

Assume this raw table of web events:

SQL
-- Snowflake SQL (read-only)
CREATE OR REPLACE TABLE shop.raw.web_events (payload VARIANT);
-- one document per row, loaded with a JSON file format

And these documents (shown here as the browser version of the table):

SQL Stand-in: a small events table

Press Run (or Ctrl + Enter) to see the result here.

In Snowflake the same query is SELECT payload:event::STRING AS event, COUNT(*) FROM shop.raw.web_events GROUP BY 1.

Filtering inside VARIANT

You can use paths in WHERE, GROUP BY and ORDER BY like any column; always cast:

SQL
-- Snowflake SQL (read-only)
SELECT payload:user.id::NUMBER AS user_id, payload:page::STRING AS page
FROM shop.raw.web_events
WHERE payload:event::STRING = 'view'
  AND payload:ms::NUMBER > 200;
SQL Stand-in: filter and aggregate on JSON fields

Press Run (or Ctrl + Enter) to see the result here.

Path functions

Task Snowflake Notes
Value at a dotted path GET_PATH(payload, 'user.id') Same as payload:user.id, but the path can be a string you build
Value by key GET(payload, 'event') Same as payload['event']
Array length ARRAY_SIZE(payload:items) NULL if not an array
Does array contain value? ARRAY_CONTAINS('A1'::VARIANT, payload:items_skus) Value must be a VARIANT
List of keys OBJECT_KEYS(payload) Array of top-level keys
Remove a key OBJECT_DELETE(payload, 'user') Returns a new object
Combine arrays ARRAY_CAT(a, b), ARRAY_DISTINCT(a) Set-like helpers
Parse safely TRY_PARSE_JSON(text) NULL instead of an error
Back to text TO_JSON(v) Compact JSON string
SQL
-- Snowflake SQL (read-only)
SELECT payload:event::STRING          AS event,
       ARRAY_SIZE(payload:items)      AS item_count,
       OBJECT_KEYS(payload)           AS top_level_keys
FROM shop.raw.web_events;

Browser equivalents: json_array_length, json_type, json_group_array, json_remove, json_valid. This stand-in counts items per purchase, and shows json_valid guarding against broken documents (like TRY_PARSE_JSON):

SQL Stand-in: array sizes and safe parsing

Press Run (or Ctrl + Enter) to see the result here.

JSON null versus SQL NULL

This trips up everyone once. JSON has its own null, and in a VARIANT it is a real value, different from SQL NULL (the absence of a value):

Situation payload:user.country returns
Key present, JSON value "DE" "DE"
Key present, JSON value null a VARIANT holding JSON null (displays null)
Key missing SQL NULL

After a cast such as ::STRING, both a JSON null and a missing key turn into SQL NULL, which is usually what you want. But IS NULL on the uncast path is true only for a missing key:

SQL
-- Snowflake SQL (read-only)
SELECT payload:user.country IS NULL                AS missing_or_sql_null,   -- JSON null is NOT here
       payload:user.country::STRING IS NULL        AS no_usable_value,       -- both cases
       IS_NULL_VALUE(payload:user.country)         AS is_json_null           -- only JSON null
FROM shop.raw.web_events;

Rule of thumb: cast first, then test for NULL. The browser distinguishes them too: json_type returns 'null' for JSON null and SQL NULL when the path is missing.

SQL Stand-in: JSON null versus missing key

Press Run (or Ctrl + Enter) to see the result here.

Aggregating nested values

To total the prices across all purchases you need the flatten step from the previous lesson, then ordinary aggregation:

SQL
-- Snowflake SQL (read-only)
SELECT payload:user.country::STRING AS country,
       SUM(i.value:price::NUMBER(10,2)) AS revenue
FROM shop.raw.web_events,
     LATERAL FLATTEN(INPUT => payload:items) i
WHERE payload:event::STRING = 'purchase'
GROUP BY country;

Runnable form:

SQL Stand-in: revenue by country from nested items

Press Run (or Ctrl + Enter) to see the result here.

A friendly view over raw JSON

Analysts should not have to memorize paths. Hide them behind a view that casts and names the fields once:

SQL
-- Snowflake SQL (read-only)
CREATE OR REPLACE VIEW shop.clean.v_web_events AS
SELECT payload:event::STRING          AS event_type,
       payload:user.id::NUMBER        AS user_id,
       payload:user.country::STRING   AS country,
       payload:page::STRING           AS page,
       payload:ms::NUMBER             AS response_ms,
       payload                        AS raw
FROM shop.raw.web_events;

Now SELECT event_type, COUNT(*) FROM v_web_events GROUP BY 1 just works, and when a producer adds a new field, you update one view. If the view becomes slow, turn it into a table or a dynamic table (see the pipelines chapter).

Schema discovery

To learn what keys exist in unfamiliar data, flatten recursively and count paths:

SQL
-- Snowflake SQL (read-only)
SELECT f.path, TYPEOF(f.value) AS type, COUNT(*) AS occurrences
FROM shop.raw.web_events,
     LATERAL FLATTEN(INPUT => payload, RECURSIVE => TRUE) f
GROUP BY 1, 2
ORDER BY occurrences DESC;

Snowflake also offers INFER_SCHEMA for staged Parquet, Avro and CSV files, which suggests column names and types before you create a table.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a VARIANT, key country is present with JSON null. What does payload:user.country IS NULL return?

    JSON null is a real VARIANT value. After ::STRING it becomes SQL NULL. Missing keys give SQL NULL right away.
  2. 2. Which function returns the number of elements of an array path?

    ARRAY_SIZE(payload:items) returns the length, or NULL if it is not an array.
  3. 3. What does TRY_PARSE_JSON do on invalid text?

    TRY_PARSE_JSON is the safe version of PARSE_JSON and returns NULL when parsing fails.
  4. 4. Why wrap raw JSON in a view that casts and names fields?

    A view hides paths and casts, so users write plain SQL.

Summary

  • Use paths in WHERE, GROUP BY and SELECT with explicit casts; GET_PATH, ARRAY_SIZE, OBJECT_KEYS and friends help navigate.
  • JSON null differs from a missing key; cast before testing IS NULL or use IS_NULL_VALUE.
  • Flatten before aggregating nested values such as line items.
  • Present raw JSON through views of typed columns.
  • Chapter 4 is complete: you can query structured and semi-structured data with Snowflake SQL. Pick up the next chapter, warehouses and performance, from the track page.

Next: Virtual Warehouse Sizing and Credits