How to Query JSON Data in Snowflake

JSON is one of those things that sounds simple until you have to work with it in a warehouse.

At first glance, Snowflake makes this look easy, and in many cases it is. You can store semi-structured data in VARIANT, pull fields with path notation, and work with nested objects without a lot of ceremony. But once you start dealing with raw JSON strings, inconsistent payloads, or arrays buried inside event data, the workflow gets less obvious.

That is where most teams get stuck.

The real job is not just “query JSON.” It is knowing when to parse it, when to extract fields directly, and when to flatten arrays into rows. Snowflake gives you a few different tools for that including PARSE_JSON, GET_PATH, and JSON_EXTRACT_PATH_TEXT. The trick is knowing which one solves which problem.

Task	What to use in Snowflake	Best for
Parse a JSON string	PARSE_JSON() or TRY_PARSE_JSON()	Converting text into VARIANT
Query a JSON field	payload:user.id or GET_PATH()	Extracting values from objects
Return a text value from raw JSON	JSON_EXTRACT_PATH_TEXT()	One-off text extraction from a JSON string
Turn array elements into rows	LATERAL FLATTEN()	Reporting on repeated values

These functions are related, but they are not interchangeable.

How Snowflake stores JSON

Snowflake stores semi-structured data in the VARIANT type. Once the JSON is in that form, you can query it directly. If the source is still plain text, parse it first.

So the first question to ask is simple:

Is this column already VARIANT, or is it still a text string?

That determines whether you need parsing at all.

Parse vs query vs flatten

Parsing

Parsing means converting a JSON string into VARIANT.

Use:

Copy Code

PARSE_JSON(raw_payload)

Or, for messy source data:

Copy Code

TRY_PARSE_JSON(raw_payload)

If the input is unreliable, TRY_PARSE_JSON is safer because it returns NULL instead of erroring out.

Querying

Querying means extracting a value from a JSON object or nested structure.

Use:

Copy Code

path notation like payload:user.id

GET_PATH(payload, ‘user.id’)

Flattening

Flattening means turning array elements into rows.

Use:

Copy Code

LATERAL FLATTEN(INPUT => payload:items)

That is where the dedicated flattening guide should take over.

How to parse JSON strings in Snowflake

If your source column contains raw JSON text, parse it first.

Copy Code

SELECT

PARSE_JSON(raw_payload) AS payload

FROM inbound_events;

If the payloads come from external systems or are not fully trustworthy, use:

Copy Code

SELECT

TRY_PARSE_JSON(raw_payload) AS payload

FROM inbound_events;

That is safer because bad rows return NULL instead of failing the whole query.

How to query top-level JSON fields

Once the data is stored as VARIANT, extracting fields is straightforward.

Copy Code

SELECT

payload:event_id::STRING AS event_id,

payload:type::STRING AS event_type,

payload:user_id::INT AS user_id

FROM raw_events;

The casts matter. Semi-structured values are flexible. Reporting logic is not.

How to query nested JSON

Nested objects use the same pattern.

Copy Code

SELECT

payload:user.id::INT AS user_id,

payload:user.email::STRING AS email,

payload:account.plan::STRING AS plan_name,

payload:account.billing.next_date::DATE AS next_billing_date

FROM raw_events;

This is usually the cleanest way to query nested JSON fields.

When to use GET_PATH

GET_PATH() does the same core job as path notation, but with an explicit path string.

Copy Code

SELECT

GET_PATH(payload, ‘account.subscription.tier’)::STRING AS subscription_tier,

GET_PATH(payload, ‘account.billing.next_date’)::DATE AS next_billing_date

FROM accounts;

Use it when:

• the path is long and you want it spelled out clearly
• you prefer explicit function syntax
• you are handling paths more dynamically

For most day-to-day queries, path notation is still easier to scan.

What JSON_EXTRACT_PATH_TEXT is useful for

JSON_EXTRACT_PATH_TEXT() is handy when the source is still a JSON string and you want a value returned as text.

Copy Code

SELECT

JSON_EXTRACT_PATH_TEXT(raw_payload, ‘user.email’) AS email

FROM inbound_events;

It is a good fit for one-off extractions from raw JSON text. If you need several fields from the same payload, it is usually cleaner to parse once into VARIANT and then query from that parsed value.

How to query arrays in Snowflake JSON

Not every array needs flattening.

If you only need to inspect or filter the array, a lighter touch is often enough.

Copy Code

SELECT

payload:user.name::STRING AS name,

ARRAY_SIZE(payload:roles) AS role_count,

ARRAY_CONTAINS(‘admin’::VARIANT, payload:roles) AS is_admin

FROM users_table;

Use this when you want to answer questions like:

• how many values are in the array
• whether a specific value exists
• whether the array is empty

How to flatten JSON arrays into rows

Here is a realistic example using order line items.

Copy Code

WITH orders AS (

SELECT PARSE_JSON(column1) AS order_data

FROM VALUES

(‘{

“order_id”: “ord_7821”,

“customer”: “Acme”,

“status”: “shipped”,

“items”: [

{“sku”: “PRO-ANNUAL”, “qty”: 1, “unit_price”: 2988.00},

{“sku”: “ADDON-SEATS-5”, “qty”: 2, “unit_price”: 199.00}

]

}’),

(‘{

“order_id”: “ord_7822”,

“customer”: “Globex”,

“status”: “pending”,

“items”: [

{“sku”: “STARTER-MONTHLY”, “qty”: 1, “unit_price”: 49.00}

]

}’)

)

SELECT

order_data:order_id::STRING AS order_id,

order_data:customer::STRING AS customer,

order_data:status::STRING AS status,

item.value:sku::STRING AS sku,

item.value:qty::INT AS qty,

item.value:unit_price::NUMBER(10,2) AS unit_price,

item.value:qty::INT * item.value:unit_price::NUMBER(10,2) AS line_total

FROM orders,

LATERAL FLATTEN(INPUT => order_data:items) AS item;

This is where semi-structured data becomes reportable.

How to inspect an unfamiliar JSON structure

If you are working with a payload you do not fully know yet, inspect the structure before writing a bigger query.

Copy Code

SELECT DISTINCT

OBJECT_KEYS(payload) AS top_level_keys

FROM inbound_events

LIMIT 20;

That gives you a quick view of the keys available at the top level, which makes the rest of the extraction easier to plan.

How to get Snowflake JSON query results into Google Sheets or Excel

Once the query works, the next step is usually not more SQL. It is getting the output somewhere the rest of the business can use it.

That is where Coefficient fits naturally. If the final table needs to land in a spreadsheet, the Snowflake Google Sheets integration gives teams a direct way to bring Snowflake data into Sheets, keep it refreshed, and avoid repeated exports.

A practical workflow looks like this:

Step 1: Connect Snowflake using Coefficient’s Snowflake connector

Step 2: Choose your SQL query or Semantic View metrics

Step 3: Import the result into Google Sheets or Excel

Step 4: Refresh it on a schedule

If you want to try it, get started.

If the query involves a lot of arrays, read How to Flatten JSON Data in Snowflake.

Best practices for querying JSON in Snowflake

• Parse only when needed
  If the column is already stored as VARIANT, skip PARSE_JSON(). Parsing again adds noise without adding value.
• Cast extracted values early
  JSON fields are flexible, but reporting logic is not. If you plan to filter, join, group, or sort on a field, cast it into the right type as soon as you extract it.
• Use path notation for straightforward extraction
  For most day-to-day queries, path notation is easier to read than function-heavy syntax. It keeps the query shorter and easier to maintain.
• Use TRY_PARSE_JSON when source data is messy
  If the input comes from logs, webhooks, or external systems, TRY_PARSE_JSON is often safer because it returns NULL instead of failing the entire query.
• Flatten arrays only when the analysis needs rows
  Not every array needs to be exploded. If you only need to inspect an array or check whether it contains a value, array functions may be enough.
• Break complex transformations into CTEs
  If the query is parsing, extracting, flattening, and aggregating all at once, splitting it into stages usually makes it easier to debug and maintain.
• Inspect unknown payloads before writing extraction logic
  Real JSON is often inconsistent. Checking the structure first helps you avoid writing queries based on assumptions that break later.

Conclusion

Once you understand the difference between parsing, querying, and flattening, Snowflake’s JSON workflow becomes much easier to work with. PARSE_JSON handles raw strings, path notation and GET_PATH() help you extract values cleanly, and LATERAL FLATTEN takes over when arrays need to become rows. That workflow is useful on its own, but it becomes even more valuable when the final result needs to be shared outside the warehouse. If you want to move query results into a spreadsheet workflow, the Snowflake Google Sheets integration gives teams a direct way to pull Snowflake data into Sheets and keep reports up to date.