How do you query JSON in SQL?

Every events table and API dump lands as a json column that everyone tiptoes around. Here are the Postgres jsonb operators decoded, and the text trap that makes queries run and still be wrong. The print-ready A4 PDF is at the bottom.

Read a key

• data->'user': json out.
• data->>'name': TEXT out.
• data#>>'{a,b}': deep path, text out.

Filter on it

• WHERE d->>'k' = 'x': match a value.
• d @> '{"pro":true}': contains.
• d ? 'key': key exists.
• A missing key returns NULL, not an error.

Arrays

• jsonb_array_elements: array becomes rows.
• d->'tags'->0: first element.
• jsonb_array_length: count items.

Cast and compute

• (d->>'amt')::NUMERIC: then SUM works.
• (d->>'ts')::DATE: then date math works.
• jsonb_typeof(d): debug what a column really holds.

Speed and build

• GIN index on jsonb: fast @> filters.
• jsonb_build_object: json back out.
• Hot keys queried constantly: promote them to real columns.
• json vs jsonb: use jsonb. Always.

Events table to clean rows

SELECT
  e.data->>'user_id'            AS user_id,
  (e.data->>'amount')::NUMERIC  AS amount,
  t.value->>0                   AS tag
FROM events e,
  jsonb_array_elements(e.data->'tags') t
WHERE e.data->>'type' = 'purchase';

jsonb_array_elements in FROM is an implicit lateral join: one output row per array item. This is the pattern that turns an API dump into something a dashboard can read.

The trap: ->> always returns text

The query runs, the number is wrong: the worst kind of bug. One arrow returns json, two arrows return text, and text sorts alphabetically. Cast at the boundary.