JSON in SQL
Read, write, query, and aggregate JSON data inside SQL. Used everywhere data lands as semi-structured payloads: event logs, API responses, user preference blobs. Every modern engine has JSON support, and the syntax differs more between engines than almost any other feature in SQL. Use these pages when you know which JSON operation you want and need the syntax for your engine.
At a glance #
-- Get a field's value out of a JSON column.
SELECT payload ->> 'user_id' FROM events; -- Postgres / DuckDB
SELECT payload:user_id::STRING FROM events; -- Snowflake
SELECT JSON_VALUE(payload, '$.user_id') FROM events; -- BigQuery / SQL Server / Oracle / MySQL
-- Turn a JSON array column into one row per element.
SELECT id, item FROM events, UNNEST(JSON_EXTRACT_ARRAY(payload, '$.items')) AS item;
-- Build JSON from rows.
SELECT JSON_OBJECT('id', id, 'email', email) FROM users; -- standard / MySQL / Postgres / DuckDBDialect comparison #
| Engine | Syntax | Gotcha |
|---|---|---|
| PostgreSQL | payload ->> 'key' | returns text; -> returns JSON — use jsonb for storage |
| MySQL | payload ->> '$.key' | path needs the $. prefix; shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) |
| SQL Server | JSON_VALUE(payload, '$.key') | scalars only; JSON_QUERY for objects/arrays |
| BigQuery | JSON_VALUE(payload, '$.key') | always returns STRING — cast before comparing |
| Snowflake | payload:key::STRING | without the cast, comparisons happen in VARIANT space |
| Oracle | JSON_VALUE(payload, '$.key') | JSON_QUERY for non-scalar values |
| SQLite | payload ->> '$.key' | -> / ->> need 3.38+; json_extract on older versions |
| DuckDB | payload ->> 'key' | Postgres-style operators; json_extract(payload, '$.key') for paths |
Pages #
- Extract a field from JSON: get a value out of a JSON column, across all major engines.
- Postgres jsonb operators —
->,->>,#>,#>>,@>,?and friends. - Unnest a JSON array into rows: explode an array column to one row per element.
- Build JSON from rows (aggregation) —
JSON_AGG,JSON_OBJECT,OBJECT_CONSTRUCT,TO_JSON. - Update a field inside JSON: modify nested values without rewriting the whole document.
JSON-in-SQL is fast for ad-hoc work and slow as a primary data model. A query that extracts a field from a small JSON column on a small table is fine; the same query against millions of rows reads and parses every JSON value on every scan. If you find yourself writing the same ->>'user_id' extraction in dozens of dashboards, that field belongs as its own column. Index it, document it, and stop paying the parse cost forever. JSON columns are the right place for truly variable payloads, not for "we didn't model it yet."