JSON

Extract a field from JSON

Q: How do I extract a field from JSON in PostgreSQL?

payload -> 'field' returns JSON; payload ->> 'field' returns text. For nested fields: payload #>> '{user, profile, country}' (text) or payload -> 'user' -> 'profile' ->> 'country'. Always cast to the target type: (payload ->> 'amount')::DECIMAL.

Q: How does Snowflake JSON path syntax work?

Colon for top-level, dot for nested: payload:user.profile.country::STRING. Cast is critical — without it, comparisons happen in VARIANT space and produce surprising results. Array elements: payload:items[0]::STRING.

Q: What's JSON_VALUE in BigQuery / SQL Server / Oracle / MySQL?

JSON_VALUE(payload, '$.user.id') — returns a scalar (always string). Use JSON_QUERY for nested objects/arrays. The $. prefix is JSONPath syntax. Always cast the result if you're comparing to numbers.

Q: What happens when the JSON field doesn't exist?

Most engines return NULL. payload ->> 'missing_key' is NULL. Errors only on syntax issues (malformed JSON in the column), not missing keys. Use COALESCE(payload ->> 'field', 'default') for fallbacks.

Q: Should I use jsonb or json on Postgres?

jsonb for almost everything. json stores the original text (preserves whitespace, key order, duplicates); jsonb stores a parsed binary form (faster reads, supports indexing, supports the rich operator set). Use json only when you need to preserve the raw text exactly.

Short answer: the syntax differs more across engines than any other JSON operation. Postgres and DuckDB use -> (returns JSON) and ->> (returns text). Snowflake uses colon path syntax: payload:user_id. BigQuery / Oracle / SQL Server / MySQL use JSON_VALUE(payload, '$.user_id'). The functional behavior is the same; only the naming differs. Always cast the result to the type you expect before using it in calculations or comparisons.

Canonical SQL #

sql

-- Postgres / DuckDB.
SELECT payload -> 'user'             AS user_json,    -- JSON value
       payload ->> 'user_id'          AS user_id_text, -- text
       (payload ->> 'amount')::NUMERIC AS amount        -- cast to number
FROM events;

-- Postgres path-style for nested values.
SELECT payload #>> '{user, profile, country}' AS country FROM events;
SELECT payload ->'user'->'profile'->>'country' AS country FROM events;

-- Snowflake.
SELECT payload:user.profile.country::STRING AS country,
       payload:amount::NUMBER               AS amount
FROM events;

-- BigQuery.
SELECT JSON_VALUE(payload, '$.user.profile.country') AS country,
       JSON_VALUE(payload, '$.amount')               AS amount_str
FROM events;

-- SQL Server / Oracle / MySQL.
SELECT JSON_VALUE(payload, '$.user.profile.country') AS country FROM events;

Example #

Loading SQL editor...

Common patterns #

sql

-- Filter by a JSON field.
SELECT * FROM events
WHERE payload ->> 'event_type' = 'purchase';        -- Postgres / DuckDB

SELECT * FROM events
WHERE JSON_VALUE(payload, '$.event_type') = 'purchase';  -- BigQuery / SQL Server / etc.

-- Group by a JSON field.
SELECT payload ->> 'country' AS country, COUNT(*)
FROM events
GROUP BY payload ->> 'country';

-- Default value when the field is missing.
SELECT COALESCE(payload ->> 'source', 'unknown') AS source FROM events;

-- Get a value with a default and the right type.
SELECT COALESCE((payload ->> 'discount')::DECIMAL, 0) AS discount FROM events;

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL	`payload ->> 'key'`	returns text; `#>>` for nested path arrays
MySQL	`payload ->> '$.key'`	`$.` path required; sugar for `JSON_UNQUOTE(JSON_EXTRACT(...))`
SQL Server	`JSON_VALUE(payload, '$.key')`	scalar only; JSON stored as `NVARCHAR`, no native type
BigQuery	`JSON_VALUE(payload, '$.key')`	always returns a STRING; cast for numeric comparisons
Snowflake	`payload:user.id::STRING`	cast, or comparisons happen in VARIANT space
Oracle	`JSON_VALUE(payload, '$.key')`	`JSON_QUERY` for objects/arrays
SQLite	`payload ->> '$.key'`	`->` / `->>` need 3.38+
DuckDB	`payload ->> 'key'`	same operators as Postgres; also `json_extract(payload, '$.user.id')`

Dialect notes #

Postgres: -> returns JSON, ->> returns text. #> and #>> for path arrays. Use the jsonb type (not json) for indexable storage. Indexes: GIN on (jsonb_col) or expression indexes on ((jsonb_col->>'field')) for hot fields.
DuckDB: same operators as Postgres (->, ->>). Also supports JSON path: json_extract(payload, '$.user.id'). JSON columns autodetect on read.
Snowflake: colon syntax payload:user.profile.country. Cast with ::STRING / ::NUMBER / ::DATE. Use VARIANT columns to store schema-flexible data — Snowflake's flagship feature for JSON-like workloads.
BigQuery: JSON_VALUE (returns scalar string), JSON_EXTRACT (returns JSON, deprecated in favor of JSON_VALUE and JSON_QUERY), JSON_EXTRACT_SCALAR. New JSON type added to replace string-typed JSON.
SQL Server: JSON_VALUE (scalar), JSON_QUERY (object/array), OPENJSON (parse to rowset). JSON stored as NVARCHAR — no native JSON type.
MySQL: -> (returns JSON) and ->> (returns text) shorthand; equivalent to JSON_EXTRACT and JSON_UNQUOTE(JSON_EXTRACT(...)). Native JSON type since 5.7.
Oracle: JSON_VALUE, JSON_QUERY, JSON_TABLE. Native JSON type since 21c. Path syntax with $.
Redshift: JSON_EXTRACT_PATH_TEXT(payload, 'user', 'profile', 'country') takes a variadic path. No native JSON type; JSON stored as text. Considerably less ergonomic than other warehouses.

FAQ #

How do I extract a field from JSON in PostgreSQL?

payload -> 'field' returns JSON; payload ->> 'field' returns text. For nested fields: payload #>> '{user, profile, country}' (text) or payload -> 'user' -> 'profile' ->> 'country'. Always cast to the target type: (payload ->> 'amount')::DECIMAL.

How does Snowflake JSON path syntax work?

Colon for top-level, dot for nested: payload:user.profile.country::STRING. Cast is critical — without it, comparisons happen in VARIANT space and produce surprising results. Array elements: payload:items[0]::STRING.

What's JSON_VALUE in BigQuery / SQL Server / Oracle / MySQL?

JSON_VALUE(payload, '$.user.id') — returns a scalar (always string). Use JSON_QUERY for nested objects/arrays. The $. prefix is JSONPath syntax. Always cast the result if you're comparing to numbers.

Why is my JSON query slow on a large table?

Without an index, every query reads and parses every JSON value. Postgres: add a GIN index for containment queries (@>), or an expression index on a specific field (CREATE INDEX ON events ((payload->>'user_id'))). Other engines: similar story. For frequently-queried fields, promote them to real columns.

What happens when the JSON field doesn't exist?

Most engines return NULL. payload ->> 'missing_key' is NULL. Errors only on syntax issues (malformed JSON in the column), not missing keys. Use COALESCE(payload ->> 'field', 'default') for fallbacks.

Should I use jsonb or json on Postgres?

jsonb for almost everything. json stores the original text (preserves whitespace, key order, duplicates); jsonb stores a parsed binary form (faster reads, supports indexing, supports the rich operator set). Use json only when you need to preserve the raw text exactly.

Warning

Cast or compare-by-string-and-suffer. payload ->> 'amount' returns text; WHERE payload ->> 'amount' > 100 does string comparison ("9" > "100" is TRUE because '9' > '1'). Cast first: WHERE (payload ->> 'amount')::DECIMAL > 100. The same trap exists in BigQuery's JSON_VALUE (always returns string), Snowflake's untyped extract, and SQL Server's JSON_VALUE. Casting is one extra token; debugging a months-old "filter mysteriously misses the 9-cent discount rows" bug is hours.