⚡ Advancedadvanced

How to query JSON data in SQL

Extract, filter and manipulate JSON stored in database columns.

Extract a JSON field (PostgreSQL)PostgreSQL

-- Table: events (data JSONB)
-- data: {"user": "alice", "action": "login", "ip": "1.2.3.4"}

SELECT
  data->>'user'   AS username,
  data->>'action' AS action,
  data->>'ip'     AS ip
FROM events;

->> extracts a JSON field as text. -> extracts as JSON.

Filter by JSON field (PostgreSQL)PostgreSQL

SELECT *
FROM events
WHERE data->>'action' = 'login'
  AND data->>'user' = 'alice';

You can use JSON field extraction in WHERE clauses.

Nested JSON extraction (PostgreSQL)PostgreSQL

-- data: {"user": {"id": 1, "name": "Alice"}, "meta": {"browser": "Chrome"}}
SELECT
  data->'user'->>'name'     AS name,
  data->'meta'->>'browser'  AS browser
FROM events;

Chain -> operators to navigate nested JSON. Use ->> at the last step for text output.

Index a JSONB field (PostgreSQL)PostgreSQL

CREATE INDEX idx_events_action
ON events ((data->>'action'));

You can index expression-based JSON extractions for fast filtering.

JSON_EXTRACT (MySQL)MySQL

SELECT
  JSON_EXTRACT(data, '$.user')   AS username,
  JSON_EXTRACT(data, '$.action') AS action
FROM events;

-- Shorthand:
SELECT
  data->>'$.user' AS username
FROM events;

MySQL uses JSON_EXTRACT() or the ->> shorthand.

Common Mistakes

→-> returns JSON, ->> returns text in PostgreSQL — use ->> for comparisons
→JSON queries without indexes are slow — create expression indexes on frequently queried fields

Common Mistakes

Related Topics