JSONB documents

Structure where you need it

Some data has no fixed shape: dish options ("extra achar", "no onion"), payment provider responses, user settings. JSONB stores JSON documents in a column — queryable and indexable — while the rest of your data stays neatly relational.

Store and query JSONB

CREATE TABLE deliveries (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  details jsonb NOT NULL
);
INSERT INTO deliveries (details) VALUES
  ('{"rider": "Dawa", "address": {"city": "Pokhara", "ward": 6}, "items": ["momo", "tea"], "paid": true}'),
  ('{"rider": "Pemba", "address": {"city": "Kathmandu", "ward": 2}, "items": ["thukpa"], "paid": false}');

SELECT details ->> 'rider' AS rider,
       details -> 'address' ->> 'city' AS city,
       jsonb_array_length(details -> 'items') AS items
FROM deliveries;

SELECT id FROM deliveries WHERE details @> '{"paid": true}';

SELECT jsonb_build_object('rider', details ->> 'rider', 'unpaid', NOT (details ->> 'paid')::boolean) FROM deliveries;

Index for containment queries: CREATE INDEX ON deliveries USING gin (details);
Don't hide everything in JSON: columns you filter, join or constrain belong in real columns.
Update one field: UPDATE … SET details = jsonb_set(details, '{paid}', 'true').

Structure where you need it

Store and query JSONB

The rest of this lesson