Topolearnjsonb lets one relational database also handle flexible, document-style data without a second system.
PostgreSQL has two JSON types. json stores the text verbatim; jsonb stores a parsed binary form — keys de-duplicated, whitespace dropped — that is faster to query and can be indexed. Use jsonb.
Reading:
doc -> 'key'returns jsonb;doc ->> 'key'returns text. Chain them:doc -> 'address' ->> 'city', or use a path:doc #>> '{address,city}'.doc @> '{"plan": "pro"}'tests containment and can use a GIN index.doc ? 'key'tests if a top-level key exists.jsonb_array_elements(doc -> 'items')turns an array into rows;jsonb_to_recordsetturns an array of objects into typed rows.- SQL/JSON path:
jsonb_path_query(doc, '$.items[*] ? (@.qty > 1)'); PostgreSQL 17 adds the standardJSON_TABLE.
Writing: jsonb_set(doc, '{address,city}', '"Gyumri"') replaces a nested value, doc || '{"vip": true}' merges top-level keys, and doc - 'key' removes one. Each update rewrites the whole value, so large documents that change often are costly.
Use jsonb for genuinely variable attributes, payloads from external APIs and settings. Anything you filter, join or constrain regularly deserves a real column — with types, constraints and normal statistics for the planner.
Example
CREATE TABLE account (id int PRIMARY KEY, profile jsonb NOT NULL);
INSERT INTO account VALUES
(1, '{"plan": "pro", "address": {"city": "Yerevan"}, "tags": ["beta", "vip"]}'),
(2, '{"plan": "free", "address": {"city": "Gyumri"}}');
SELECT id, profile ->> 'plan' AS plan,
profile -> 'address' ->> 'city' AS city
FROM account WHERE profile @> '{"plan": "pro"}'; -- 1 | pro | Yerevan
SELECT id FROM account WHERE profile -> 'tags' ? 'vip'; -- 1
UPDATE account SET profile = jsonb_set(profile, '{address,city}', '"Vanadzor"')
WHERE id = 2 RETURNING profile -> 'address'; -- {"city": "Vanadzor"}
CREATE INDEX account_profile_gin ON account USING gin (profile jsonb_path_ops);Remember
- -> returns jsonb, ->> returns text
- @> containment queries can use a GIN index
- Promote frequently filtered fields to real columns
Watch out for
- Comparing profile -> 'age' > 30: -> returns jsonb, so cast (profile ->> 'age')::int instead.