Postgres JSONB skill for Claude Code

JSONB is used well when it holds data whose shape varies or is owned by someone else (payloads, user-defined attributes, settings), keys the application filters or joins on are promoted to real columns or indexed expressions, and every document is validated at the database boundary. A table that is one id plus one data jsonb column is a smell.

Columns, JSONB, or child table?

Data	Choose
Fixed attributes every row has, filtered, joined, or constrained (FK, UNIQUE)	regular columns
Sparse, varying attributes (product specs by category, user-defined fields)	JSONB
Raw external payloads kept for audit/replay (webhooks, API responses)	JSONB, append-only, plus extracted columns for what you query
Repeating items you query individually or update often (order lines, tags with metadata)	child table
Simple list of scalars (tags)	text[] with GIN, or JSONB array
Settings blob read and written whole by the app	JSONB

json (not jsonb) only when you must preserve key order, duplicates, or exact whitespace; it cannot be indexed with GIN and reparses on every access.

Workflow

- [ ] 1. Justify JSONB per the table above; list keys that will be filtered on
- [ ] 2. Add validation (CHECK constraints, IS JSON, optional schema check)
- [ ] 3. Choose index strategy per query pattern
- [ ] 4. Write queries with index-compatible operators
- [ ] 5. Promote hot keys to generated columns when needed
- [ ] 6. Verify plans and row estimates with EXPLAIN ANALYZE

2. Validation

ALTER TABLE products ADD COLUMN attrs jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE products ADD CONSTRAINT attrs_is_object
  CHECK (jsonb_typeof(attrs) = 'object');

ALTER TABLE products ADD CONSTRAINT attrs_weight_numeric
  CHECK (NOT attrs ? 'weight_g' OR jsonb_typeof(attrs -> 'weight_g') = 'number');

• IS JSON OBJECT / IS JSON ARRAY predicates exist in PG16+; useful when validating text input before casting.
• Full JSON Schema validation is not built in. Options: the pg_jsonschema extension where available (Supabase ships it), or validate in the application with a schema version field (attrs->>'v') checked by a constraint.
• Add constraints as NOT VALID then VALIDATE CONSTRAINT on large tables to avoid a long lock (see schema-migrations).

3. Index strategy

Query	Index
attrs @> '{"color":"red"}' containment, many different keys	GIN (attrs jsonb_path_ops) - smaller, faster, supports @>, @?, @@ only
Key existence attrs ? 'sale', `?	, ?&` plus containment
Equality/range/sort on one known key	B-tree expression: ((attrs->>'sku')) or a typed cast
One key queried in most requests	stored generated column + B-tree

CREATE INDEX CONCURRENTLY products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
CREATE INDEX CONCURRENTLY products_brand_idx ON products ((attrs->>'brand'));
CREATE INDEX CONCURRENTLY products_price_idx ON products (((attrs->>'price')::numeric));

The query must use the exact indexed expression: WHERE (attrs->>'price')::numeric < 50 matches the last index; WHERE attrs->'price' < '50' does not.

4. Operators that use indexes

-- Containment (GIN): products that are red AND waterproof
SELECT id FROM products WHERE attrs @> '{"color": "red", "waterproof": true}';

-- Array element containment (GIN)
SELECT id FROM products WHERE attrs @> '{"tags": ["outdoor"]}';

-- jsonpath predicate (GIN jsonb_path_ops supports @? and @@)
SELECT id FROM products WHERE attrs @? '$.sizes[*] ? (@ == "XL")';

-- Extraction for display (no index needed)
SELECT id, attrs->>'brand' AS brand, attrs #>> '{dimensions,width}' AS width FROM products;

Operator	Returns / tests	GIN?
-> / ->>	element as jsonb / as text	no (B-tree on expression)
#> / #>>	path as jsonb / text	no
@>	contains	yes (both opclasses)
?, `?	, ?&`	top-level key exists
@?, @@	jsonpath exists / predicate	yes

attrs->>'color' = 'red' does not use a GIN index; rewrite as attrs @> '{"color":"red"}' or add an expression index.

5. Promote hot keys

ALTER TABLE events ADD COLUMN event_type text
  GENERATED ALWAYS AS (payload->>'type') STORED;
CREATE INDEX CONCURRENTLY events_type_created_idx ON events (event_type, created_at DESC);

• Write STORED explicitly; PG18 defaults generated columns to virtual, which cannot be indexed.
• Adding a stored generated column rewrites the table; on large tables backfill a normal column in batches and maintain it in the write path, or schedule the rewrite.
• Once a key needs a foreign key, uniqueness, or NOT NULL, it belongs in a real column.

Updates

UPDATE products SET attrs = jsonb_set(attrs, '{price}', to_jsonb(19.99), true) WHERE id = $1;
UPDATE products SET attrs = attrs || '{"sale": true}'::jsonb WHERE id = $1;    -- merge top level
UPDATE products SET attrs = attrs - 'legacy_field' WHERE id = $1;              -- remove key
UPDATE products SET attrs = attrs #- '{dimensions,depth}' WHERE id = $1;       -- remove nested

• Every update rewrites the whole value (TOAST-compressed documents over ~2 KB are rewritten out of line). Large documents with frequently changing small fields belong in columns or a child table.
• || is a shallow merge: nested objects are replaced, not merged.
• jsonb_set with a NULL new value returns NULL and wipes the column; use jsonb_set_lax or coalesce when the value can be null.
• Concurrent read-modify-write in application code loses updates; do the change in one UPDATE statement as above.

SQL/JSON (PG16-17+)

-- PG17: shred a JSON array into rows
SELECT o.id, li.*
FROM orders o,
     JSON_TABLE(o.payload, '$.items[*]' COLUMNS (
       sku text PATH '$.sku',
       qty int PATH '$.qty',
       price numeric PATH '$.price'
     )) AS li;

-- Earlier versions
SELECT o.id, i->>'sku' AS sku, (i->>'qty')::int AS qty
FROM orders o, jsonb_array_elements(o.payload->'items') AS i;

JSON_VALUE, JSON_QUERY, and JSON_EXISTS (PG17) add typed extraction with ON ERROR / ON EMPTY behavior; prefer them over chained casts when the version allows.

Gotchas

• The planner has no per-key statistics inside JSONB: @> estimates default to a small fixed selectivity, causing bad join plans. Extended statistics on expressions (PG14+) help: CREATE STATISTICS products_brand_stats ON (attrs->>'brand') FROM products; ANALYZE products;
• ->> returns text; comparing numbers as text sorts "100" < "9". Cast explicitly.
• JSON null and SQL NULL differ: attrs->'x' can be 'null'::jsonb, which IS NULL does not catch. Test with jsonb_typeof(attrs->'x') = 'null'.
• Numbers in JSONB are numeric; extremely large integers survive, but drivers may parse them as floats in the application.
• Key names repeat in every row; millions of rows with long key names cost real storage.
• ORMs often read and write the whole document; a partial-update helper or raw SQL avoids clobbering concurrent changes.

Verification

• EXPLAIN (ANALYZE, BUFFERS) for each JSON filter shows the intended GIN or expression index, and estimated vs actual rows are within roughly 10x (add expression statistics if not).
• Constraint tests: inserting a wrongly typed key fails with the named constraint.
• Update tests: concurrent patches to different keys both survive.
• pg_column_size(attrs) average checked; documents over a few KB with hot fields flagged for promotion.