JSON in SQL (Snowflake, BigQuery, Postgres, DuckDB) cheat sheet

Extracting, flattening, and typing semi-structured JSON across the four engines you actually use, side by side.

SQL & DatabasesIntermediate5 sections

Extract a scalar field

select payload:user.id::int from t -- Snowflake: Colon-path syntax on VARIANT. Always cast at the end — VARIANT comparisons without casts cause silent type surprises.
select json_value(payload, '$.user.id') from t -- BigQuery: JSON_VALUE returns a string scalar; use int64(json_query(...)) or lax_int64 for typed access on the JSON type.
select payload->'user'->>'id' from t -- Postgres: -> returns jsonb, ->> returns text. Cast with ::int at the end. jsonb_path_query for JSONPath.
select payload->'user'->>'id' from t -- DuckDB: DuckDB mirrors Postgres operators; json_extract(payload, '$.user.id') also works.

select f.value:sku::text from t, lateral flatten(input => payload:items) f -- Snowflake: FLATTEN is a table function; f.value is each element, f.index the position. outer => true keeps empty arrays.
select item from t, unnest(json_query_array(payload, '$.items')) item -- BigQuery: JSON_QUERY_ARRAY turns a JSON array into an ARRAY<JSON> you can UNNEST.
select e ->> 'sku' from t, jsonb_array_elements(payload->'items') e -- Postgres: Set-returning function in the FROM clause; use jsonb_array_elements_text for string arrays.
select unnest(from_json(payload, '["json"]')) from t -- DuckDB: Or cast to a typed list first — from_json with a structure gives you real columns immediately.

select object_construct('id', id, 'tags', array_agg(tag)) from t group by id -- Snowflake: object_construct skips NULL values by default; object_construct_keep_null keeps them.
select to_json(struct(id, name)) from t -- BigQuery: Compose structs then serialize. json_object('k', v) builds objects key by key.
select jsonb_build_object('id', id, 'tags', jsonb_agg(tag)) from t group by id -- Postgres: jsonb_agg aggregates rows into an array; row_to_json converts whole rows.
select to_json(struct_pack(id := id, name := name)) from t -- DuckDB: struct_pack then to_json — same shape as BigQuery's approach.

create table typed as select payload:id::int id, payload:ts::timestamp_ntz ts from raw -- Snowflake: The bronze-to-silver move: land VARIANT, project typed columns downstream. Snowflake stores VARIANT columnar, so pruning still works.
select json_value(payload.user.email) from t -- BigQuery: The native JSON type (vs STRING) enables dot access and cheaper storage; lax_* accessors tolerate type drift.
select * from jsonb_to_recordset(payload->'items') as items(sku text, qty int) -- Postgres: Cast an array of objects straight into a typed rowset with named columns.
select * from read_json('f.json', columns = {id:'BIGINT', ts:'TIMESTAMP'}) -- DuckDB: Explicit column spec beats inference for stable pipelines.

payload:user.id = 42 -- may be false when id is VARIANT string: Untyped comparisons do implicit coercion differently per engine. Rule: cast to a concrete type before comparing or joining.
json_value returns NULL on arrays/objects: In BigQuery, JSON_VALUE only returns scalars; JSON_QUERY returns JSON fragments. Mixing them up silently yields NULLs.
jsonb vs json in Postgres: Always jsonb — binary, indexable (GIN), deduplicated keys. Plain json preserves key order and duplicates but can't be indexed usefully.
select payload from t where payload:type = 'order' -- prunes in Snowflake: Snowflake and BigQuery prune on extracted JSON paths surprisingly well, but a materialized typed column is always faster and cheaper.

From DataLane — tutorials at/blog, practice SQL live in theplayground.