Query JSON columns

Use the engine's JSON path operator to pull the field out, and cast it to the right type. For anything you query often, do that extraction once during ingestion and store the field in a proper typed column.

Syntax by engine

-- BigQuery (JSON stored in a STRING or JSON column)
SELECT JSON_VALUE(payload, '$.user.id') AS user_id,
       CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC) AS amount
FROM events;

-- Snowflake (VARIANT column)
SELECT payload:user.id::STRING AS user_id,
       payload:amount::NUMBER(10,2) AS amount
FROM events;

-- Postgres (jsonb column)
SELECT payload -> 'user' ->> 'id' AS user_id,
       (payload ->> 'amount')::numeric AS amount
FROM events;

If the JSON is stored as text in Snowflake, run PARSE_JSON(col) first. Older BigQuery code uses JSON_EXTRACT_SCALAR. Databricks uses payload:user.id on a string column too.

Why it can be slow

When the data is a JSON string, every query must read the whole blob and parse it for every row, even if you want one field of fifty. Columnar storage and pruning cannot help because the engine does not know the structure inside the string. Types are also all strings until you cast them, and a bad cast can fail the query or return NULL depending on the function.

Snowflake VARIANT and BigQuery's native JSON type store the data in an optimised internal form, which helps, though a typed column still wins.

What to do about it

• Extract the fields you filter and join on into real columns in the staging layer (user_id, event_type, amount), and keep the full raw payload for everything else.
• Add the extraction to the load, so it runs once, not at every query.
• Watch for schema drift. A field that is sometimes a number and sometimes a string will break casts. Handle it where you extract.

Arrays inside JSON

A path that points at an array returns the whole array. To get one row per element you need to explode it, with UNNEST or FLATTEN. That is the next problem, and it changes the grain of the result.