PostgreSQL JSONB vs JSON — Operators, Indexing, Decision Tree
Fast recall
Quick revision
Core answer
json stores raw text and re-parses on every read; `jsonb` stores a parsed binary format, supports operators (->, ->>, @>, ?, ?&, ?|, #>), and can be indexed with GIN for containment queries. Use jsonb for schema-flexible attributes (user prefs, product metadata, integration payloads). Use typed relational columns for data you filter, join, or aggregate on hot paths — typed B-tree indexes always outperform GIN for that. Generated columns let you promote JSONB fields to typed columns as access patterns emerge.Whether you can explain the difference between json and jsonb, navigate the operator suite, pick the right GIN opclass, and make an informed call between JSONB and a normalized schema for a given access pattern.
Treating PostgreSQL as a document database — dumping everything into a single JSONB column, losing type safety, and later finding that hot filters cannot use the index because the query uses `->>` text extraction instead of `@>` containment.
Mention `jsonb_path_ops` for a smaller, faster containment-only GIN index, the JSONPath query language (`@?`, `@@`, `jsonb_path_query`), pg_jsonschema for validation, and the generated-column pattern for promoting hot JSONB fields into typed columns.
Interview-ready guide
Interview answer
JSONB is PostgreSQL's binary JSON type. Unlike the plain JSON type, which keeps the input text and re-parses it on every read, JSONB parses once at write time, sorts keys, deduplicates values, and supports an operator suite plus GIN indexing.
Operators you use most
->>extracts a field as text, used in WHERE for equality filters->extracts a field as JSONB for further navigation@>checks containment,WHERE data @> '{"role":"admin"}'and this is the operator that benefits from a GIN index?checks whether a top-level key exists
Indexing
- A GIN index with the
jsonb_path_opsopclass is the default for containment queries — it is smaller and faster than the default opclass - For equality on a specific extracted field, create an expression index on
(attrs->>'email')
JSONB vs typed columns
- Use JSONB for schema-flexible attributes, product metadata, feature flags, payloads — data whose shape legitimately varies between rows
- Use typed columns for data the application filters, joins, or aggregates on hot paths — price, status, user_id, timestamps
- When a JSONB path becomes hot, promote it to a generated column so you get a typed, indexed view without changing the write path
Gotchas to mention
- Every JSONB update rewrites the whole row, which is expensive on very large JSONB values
- Schema drift is real — use pg_jsonschema or a CHECK constraint to validate shape at write time
PostgreSQL JSONB vs JSON — Operators, Indexing, Decision Tree
Direct Answer
json stores raw text and re-parses on every read; jsonb stores a parsed binary format, supports operators (->, ->>, @>, ?, ?&, ?|, #>), and can be indexed with GIN for containment queries. Use jsonb for schema-flexible attributes (user prefs, product metadata, integration payloads). Use typed relational columns for data you filter, join, or aggregate on hot paths — typed B-tree indexes always outperform GIN for that. Generated columns let you promote JSONB fields to typed columns as access patterns emerge.
Code Example
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL,
category TEXT NOT NULL,
attributes JSONB NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO products (name, price, category, attributes) VALUES
('Widget Pro', 29.99, 'electronics',
'{"color":"red", "weight_kg":0.5, "tags":["sale","new"], "in_stock":true}');
-- OPERATOR REFERENCE
-- -> returns jsonb: attributes -> 'color' -> '"red"'
-- ->> returns text: attributes ->> 'color' -> 'red'
-- #> returns jsonb: attributes #> '{specs,cpu}'
-- #>> returns text: attributes #>> '{specs,cpu}'
-- @> containment: attributes @> '{"color":"red"}'::jsonb
-- <@ contained by: '{"color":"red"}'::jsonb <@ attributes
-- ? key exists: attributes ? 'weight_kg'
-- ?| any key: attributes ?| array['color','size']
-- ?& all keys: attributes ?& array['color','weight_kg']
-- GIN INDEX VARIANTS
-- Default: supports @>, ?, ?|, ?&; larger index
CREATE INDEX idx_products_attrs_full ON products USING GIN (attributes);
-- jsonb_path_ops: supports ONLY @>; ~30-50% smaller, 2-3x faster lookups
CREATE INDEX idx_products_attrs_paths
ON products USING GIN (attributes jsonb_path_ops);
-- Expression index for specific-field equality via ->>
CREATE INDEX idx_products_brand ON products ((attributes ->> 'brand'));
-- USING THE INDEX
-- Containment uses GIN (fastest path)
SELECT name FROM products
WHERE attributes @> '{"color":"red", "in_stock":true}'::jsonb;
-- Key-existence uses GIN
SELECT name FROM products WHERE attributes ? 'weight_kg';
-- ->> comparison does NOT use the default GIN index; needs expression index
SELECT name FROM products WHERE attributes ->> 'brand' = 'Acme';
-- Update a single key (rewrites entire row per MVCC)
UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"blue"', true)
WHERE id = 1;
-- Remove a key
UPDATE products SET attributes = attributes - 'tags' WHERE id = 1;
-- Expand JSONB into rows
SELECT name, kv.key, kv.value
FROM products, jsonb_each_text(attributes) AS kv
WHERE category = 'electronics';
-- JSONPath (PG 12+): complex predicates
SELECT * FROM products
WHERE attributes @? '$.tags[*] ? (@ == "sale")';
-- Generated column: promote a hot JSONB field to typed + indexed
ALTER TABLE products
ADD COLUMN brand TEXT GENERATED ALWAYS AS (attributes->>'brand') STORED;
CREATE INDEX idx_products_brand_typed ON products (brand);
Common Mistakes & Pitfalls
Treating PostgreSQL as a document database — dumping everything into a single JSONB column, losing type safety, and later finding that hot filters cannot use the index because the query uses ->> text extraction instead of @> containment.
Check your understanding
Answer these without looking back at the article.
- What is the difference between JSONB's default GIN opclass and `jsonb_path_ops`, and when does each win?
- How do you make `WHERE attributes->>'email' = ?` use an index?
- How do you enforce a schema on a JSONB column and why would you do that?
- What is the generated-column promotion pattern for hot JSONB fields?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.