JSON & JSONB

Postgres · Reference cheat sheet

📋 Overview

json stores text as-is; jsonb stores a binary decomposed form — better for indexing and containment queries. Prefer jsonb for operational query patterns.

🔧 Core concepts

💡 Examples

Query fields:

SELECT payload->>'event'
FROM events
WHERE payload @> '{"type":"signup"}';

GIN index:

CREATE INDEX CONCURRENTLY events_payload_gin
  ON events USING GIN (payload jsonb_path_ops);

Update key:

UPDATE users
SET prefs = jsonb_set(prefs, '{theme}', '"dark"', true)
WHERE id = 42;

⚠️ Pitfalls

• Unindexed payload->>'x' = on large tables seq-scans — express as jsonb ops + GIN when possible.
• Turning everything into JSONB abandons relational constraints — hybrid carefully.
• json vs jsonb equality and duplicate-key behavior differ.

🔗 Related

• indexes.md
• explain_analyze.md
• extensions.md
• migrations_ops.md