JSONB: operators, containment and SQL/JSON

conceptThe concept

-> returns jsonb, ->> returns text, #>> '{a,b}' follows a path. Since 14 you can also subscript: attrs['brand']. @> tests containment (attrs @> '{"color":"red"}'), ? tests whether a key exists. SQL/JSON path (jsonb_path_query, @?, @@) adds filters such as '$.weight_kg ? (@ > 5)'.

Build JSON with jsonb_build_object and jsonb_agg; modify with || (merge), - (remove a key) and jsonb_set. JSON_TABLE (17) turns a JSON document into a relational row set with typed columns.

A GIN index on a jsonb column supports @>, ?, ?| and ?&; the jsonb_path_ops operator class is smaller and faster but supports only containment and path matching. For one hot key, a B-tree expression index on (attrs->>'brand') is often better.

whyWhy it matters

Product catalogues, event payloads and settings genuinely vary in shape. jsonb keeps them queryable without a column per possible attribute. The trade-off is weaker typing, no foreign keys inside documents, and statistics the planner can only partly use.

Use caseReal-world scenario

Only electronics have warranty_years, colours are optional, and new attributes appear each season. The shop keeps stable fields (price, stock, category) as columns and the long tail in attrs.

Try itExamples

Run and edit these in the interactive lesson.

SELECT name,
       attrs -> 'brand' AS brand_json,
       attrs ->> 'brand' AS brand_text,
       attrs['weight_kg'] AS weight,
       (attrs ->> 'warranty_years')::int AS warranty
FROM products
WHERE category = 'electronics'
ORDER BY id
LIMIT 5;

SELECT count(*) FILTER (WHERE attrs @> '{"color": "red"}') AS red,
       count(*) FILTER (WHERE attrs ? 'warranty_years') AS has_warranty,
       count(*) FILTER (WHERE NOT attrs ? 'color') AS no_color,
       count(*) FILTER (WHERE attrs @? '$.weight_kg ? (@ > 10)') AS heavy
FROM products;

With 20,000 events the planner may still prefer a sequential scan for a common value; disabling seq scans for one transaction shows the index is usable.

BEGIN;
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);
SET LOCAL enable_seqscan = off;
EXPLAIN SELECT count(*) FROM events WHERE payload @> '{"path": "/checkout", "device": "desktop"}';
ROLLBACK;

SELECT c.name,
       jsonb_build_object(
         'customer', c.name,
         'orders', count(o.id),
         'last_three', (SELECT jsonb_agg(x ORDER BY x.created_at DESC) FROM (
             SELECT id, status, created_at FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 3) x)
       ) AS doc
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.id = 42
GROUP BY c.id;

SELECT attrs,
       attrs || '{"on_sale": true}' AS merged,
       attrs - 'color' AS without_color,
       jsonb_set(attrs, '{weight_kg}', '1.5') AS reweighed
FROM products WHERE id = 1;

SELECT jt.*
FROM events e,
     JSON_TABLE(e.payload, '$' COLUMNS (
       path   text PATH '$.path',
       device text PATH '$.device',
       ms     int  PATH '$.ms'
     )) AS jt
WHERE e.kind = 'checkout'
ORDER BY e.id
LIMIT 5;

internalsUnder the hood

json stores the original text (whitespace, key order, duplicate keys); jsonb parses once, removes duplicates and sorts keys, so reads are faster and writes slightly slower. Updating one key rewrites the whole value, and values over about 2 kB are compressed and moved out of line (TOAST), so very large, frequently updated documents are expensive.

Avoid / preferDo this, not that

SELECT name FROM products WHERE attrs ->> 'weight_kg' > '5';

SELECT name FROM products WHERE (attrs ->> 'weight_kg')::numeric > 5;

CREATE TABLE customers (id integer PRIMARY KEY, doc jsonb);  -- everything inside doc

CREATE TABLE customers (
  id integer PRIMARY KEY, email text NOT NULL UNIQUE, country text NOT NULL,
  preferences jsonb NOT NULL DEFAULT '{}'
);

PitfallsCommon mistakes

• Comparing attrs->'price' > 10: that compares jsonb with a number. Extract with ->> and cast, or use a jsonpath filter.
• Expecting a GIN index to help attrs->>'brand' = 'Acme'. GIN serves @> and ?; rewrite as attrs @> '{"brand":"Acme"}' or add an expression index.
• Putting core relational fields (customer_id, status, amounts) into jsonb. You lose types, constraints, foreign keys and good statistics.

LimitsWhen not to use it

Do not use JSONB to avoid schema design. You lose types, NOT NULL, foreign keys and accurate planner statistics, and every value repeats its key names on disk.

SecuritySecurity notes

Build JSON with jsonb_build_object or bind parameters, never by concatenating strings, or user input can inject extra keys. Validate shapes with CHECK constraints such as jsonb_typeof(attrs) = 'object'.

ChallengeTry it yourself

SELECT attrs ->> 'brand' AS brand,
       count(*) AS products,
       round(avg((attrs ->> 'warranty_years')::int), 2) AS avg_warranty
FROM products
WHERE attrs ? 'warranty_years'
GROUP BY 1
ORDER BY 1;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• JSON types
• JSON functions and SQL/JSON path