Core JSONB operators: access, search, and manipulation

Capsule overview

PostgreSQL has roughly a dozen JSONB operators and a handful of functions that come with them. Most devs learn ->> in the first week and Google the rest every time. That works — until you need to write a query that filters by containment over nested JSON, or you need to distinguish "key with a null value" from "nonexistent key," or you need to report to your team "this index isn't going to be used because operator X isn't GIN-indexable."

This capsule gives you the complete map. You don't memorize loose operators — you group them by purpose: access (extract a value), search (test a condition), manipulation (return a modified JSONB). That grouping is what connects directly to capsule 05: search operators are the ones that benefit from GIN. Access ones don't (they need expression indexes). Manipulation ones are never indexed — they're for writing.

By the end you'll be able to write any JSONB query without consulting the docs, you'll know which operator to choose based on the business question, and you'll have a clear foundation that capsule 05 will exploit for indexing.

Mental model: three families, three purposes

┌────────────────────────────────────────────────────────────────────┐
│                                                                    │
│  ACCESS              SEARCH                MANIPULATION            │
│  "extract"           "verify"              "return modified"       │
│                                                                    │
│  ->   ->>            @>   <@               ||                      │
│  #>   #>>            ?    ?|   ?&          -                       │
│                                            jsonb_set               │
│                                            jsonb_insert            │
│                                                                    │
│  → returns a value   → returns a boolean   → returns a new jsonb   │
│  → expression index  → GIN-indexable       → not indexed           │
│                                                                    │
└────────────────────────────────────────────────────────────────────┘

Access: "give me the value of key X" → returns the value (jsonb or text). Useful in SELECT and sometimes in WHERE on a specific field (in which case you need an expression index, not GIN).

Search: "does this JSON contain Y?" → returns a boolean. Useful in WHERE over the whole JSONB. These are the ones GIN speeds up.

Manipulation: "modify this JSON and give me the new result" → returns a new JSONB. Useful in UPDATE. Not indexed — they're for writing.

Memorize that table. Every time you're unsure about an operator, place it in one of the three columns and you'll know what to expect from it.

Family 1: Access operators

Access operators extract a value from the JSONB. There are four. The difference between them boils down to two questions:

1. Do you return jsonb or text? → one angle bracket (>) returns jsonb, two (>>) return text.
2. Do you access one level or several? → a single arrow (->) for one level, a hash (#>) by path.

That gives you the four combinations:

Setup for the examples

-- Demo table
DROP TABLE IF EXISTS demo;
CREATE TABLE demo (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO demo (data) VALUES
('{
  "name": "Ana",
  "age": 30,
  "city": "Buenos Aires",
  "tags": ["admin", "active"],
  "address": {
    "street": "Av. Corrientes 1234",
    "country": "AR",
    "coordinates": {"lat": -34.60, "lng": -58.38}
  }
}'::jsonb);

-> (returns jsonb)

SELECT data->'name' FROM demo;
-- Output: "Ana"          ← it's jsonb (a JSON string, with quotes)

SELECT data->'age' FROM demo;
-- Output: 30             ← it's jsonb (a number)

SELECT data->'tags' FROM demo;
-- Output: ["admin", "active"]   ← it's jsonb (an array)

SELECT data->'address' FROM demo;
-- Output: {"street": "...", "country": "AR", ...}   ← it's jsonb (an object)

-> always returns jsonb. That lets you chain:

-- "country" of the "address"
SELECT data->'address'->'country' FROM demo;
-- Output: "AR"   ← jsonb

-- "lat" of "coordinates" of "address"
SELECT data->'address'->'coordinates'->'lat' FROM demo;
-- Output: -34.60   ← jsonb (numeric)

It also works with array indexes:

SELECT data->'tags'->0 FROM demo;
-- Output: "admin"   ← jsonb (string), first element

SELECT data->'tags'->-1 FROM demo;
-- Output: "active"  ← jsonb, last element (negative index)

->> (returns text)

Identical to -> but the result is text instead of jsonb. It's what you want when you're going to compare against a SQL string or return it to the app.

SELECT data->>'name' FROM demo;
-- Output: Ana        ← text (no quotes)

SELECT data->>'age' FROM demo;
-- Output: 30         ← text (the string "30", not a number)

-- Useful in WHERE
SELECT * FROM demo WHERE data->>'city' = 'Buenos Aires';
-- Works; you're comparing text with text

Common trap with ->>: it converts everything to text. If the original value is a number (age: 30), ->> gives you the string "30". If you need to compare as a number:

-- This doesn't work the way you expect:
SELECT * FROM demo WHERE data->>'age' > 25;
-- Compares strings: "30" > "25" happens to be true (lexicographic order),
-- but "100" > "25" would be false (lex). A latent bug.

-- This does:
SELECT * FROM demo WHERE (data->>'age')::int > 25;
-- Or better:
SELECT * FROM demo WHERE (data->'age')::int > 25;

Lesson: if the value is numeric and you're going to do math/numeric comparisons, cast explicitly.

#> and #>> (access by path)

When you want to reach a deeply nested value, chaining -> gets verbose. #> takes an array of keys and goes straight there:

-- Equivalent:
SELECT data->'address'->'coordinates'->'lat' FROM demo;
SELECT data#>'{address,coordinates,lat}' FROM demo;
-- Output: -34.60   ← jsonb

-- Text version:
SELECT data#>>'{address,coordinates,lat}' FROM demo;
-- Output: -34.60   ← text

The path is a PostgreSQL array ('{a,b,c}') or it can be passed as ARRAY['address','coordinates','lat']. For arrays, the indexes go in as strings:

SELECT data#>>'{tags,0}' FROM demo;
-- Output: admin   ← text, first element

When to use which:

• -> / ->>: when you go one level and the code reads better.
• #> / #>>: when you go deep (3+ levels) or when the path comes from a variable.

-- Path from a variable (typical case in dynamic queries)
DO $$
DECLARE
  key_path TEXT[] := ARRAY['address', 'country'];
BEGIN
  RAISE NOTICE '%', (SELECT data#>>key_path FROM demo LIMIT 1);
END $$;
-- NOTICE:  AR

Indexability of the access operators

Access operators are NOT sped up by GIN. GIN is designed for search (containment, existence), not for "extract this field." If you want to speed up a WHERE data->>'user_id' = '42', you need an expression index:

-- Index on the specific expression
CREATE INDEX events_user_id_idx ON events ((payload->>'user_id'));

-- Now this query uses the index:
EXPLAIN ANALYZE SELECT * FROM events WHERE payload->>'user_id' = '42';
-- Index Scan using events_user_id_idx

This is a technique you'll use a lot. Capsule 05 covers it in depth. For now, remember: access → expression index. Search → GIN.

Family 2: Search operators

These are the ones GIN speeds up. They return a boolean and they're the stars of WHERE queries over JSONB. There are two sub-groups: containment and existence.

Containment: @> and <@

@> is the most important operator in the whole capsule. It means "the JSONB on the left contains the JSONB on the right."

-- does the JSON contain this key with this value?
SELECT '{"a": 1, "b": 2}'::jsonb @> '{"a": 1}'::jsonb;
-- true

SELECT '{"a": 1, "b": 2}'::jsonb @> '{"a": 2}'::jsonb;
-- false (the value doesn't match)

SELECT '{"a": 1, "b": 2}'::jsonb @> '{"c": 3}'::jsonb;
-- false (the key doesn't exist)

-- Works over nested structures
SELECT '{"a": {"b": {"c": 1}}}'::jsonb @> '{"a": {"b": {"c": 1}}}'::jsonb;
-- true

SELECT '{"a": {"b": {"c": 1}}}'::jsonb @> '{"a": {"b": {"c": 1, "d": 2}}}'::jsonb;
-- false (the right side has an extra key)

Containment over arrays: one array contains another if the second is a sub-array (a subset, not necessarily in order):

SELECT '["a", "b", "c"]'::jsonb @> '["a"]'::jsonb;
-- true

SELECT '["a", "b", "c"]'::jsonb @> '["b", "a"]'::jsonb;
-- true (subset, order doesn't matter)

SELECT '["a", "b", "c"]'::jsonb @> '["d"]'::jsonb;
-- false

<@ is the inverse: the left side is contained in the right one.

SELECT '{"a": 1}'::jsonb <@ '{"a": 1, "b": 2}'::jsonb;
-- true (1 is a subset of {a,b})

In practice @> is the one you'll use 95% of the time. The idiomatic form is "filter records whose jsonb column contains this pattern":

-- Returns events with action=purchase AND country=US
SELECT * FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';

That query is indexable with GIN. The GIN will jump straight to the candidate rows without reading the whole table. That's the module's core pattern.

Existence: ?, ?|, ?&

These ask whether a key (not its value) exists at the top level of the JSONB.

SELECT '{"a": 1, "b": 2}'::jsonb ? 'a';
-- true (the key 'a' exists)

SELECT '{"a": 1, "b": 2}'::jsonb ? 'c';
-- false

SELECT '{"a": null}'::jsonb ? 'a';
-- true (the key exists, even though its value is JSON null)

Important difference with ->>:

• data ? 'a' tells you whether the key exists.
• data->>'a' IS NOT NULL tells you whether the key exists AND has a non-null value.

If your JSON can have "key with a null value" as a meaningful case (e.g., "the field was intentionally left empty"), ? is the right tool to detect it.

?| (any of) and ?& (all of):

-- does any of these keys exist?
SELECT '{"a": 1, "b": 2}'::jsonb ?| ARRAY['x', 'b'];
-- true (b exists, that's enough)

-- do all of these keys exist?
SELECT '{"a": 1, "b": 2}'::jsonb ?& ARRAY['a', 'b'];
-- true

SELECT '{"a": 1, "b": 2}'::jsonb ?& ARRAY['a', 'c'];
-- false (c doesn't exist)

Careful with arrays: ? also works over arrays of strings, not objects:

-- does the array contain this string?
SELECT '["admin", "active"]'::jsonb ? 'admin';
-- true

SELECT '[{"role": "admin"}]'::jsonb ? 'admin';
-- false (the array has an object, not the string "admin")

For "the array contains the object," use @>:

SELECT '[{"role": "admin"}]'::jsonb @> '[{"role": "admin"}]'::jsonb;
-- true

Search indexability — the big difference

This table is the heart of the jsonb_ops vs jsonb_path_ops decision that we're going to explore in capsule 05. For now, internalize:

• If your queries only use @>, jsonb_path_ops is faster and more compact.
• If your queries also use ? / ?| / ?&, you need jsonb_ops (the default).

Family 3: Manipulation operators

These modify a JSONB and return a new version. They're useful in UPDATE. They aren't indexed because they're write operations, not search conditions.

|| (concatenation / merge)

-- Shallow merge (not recursive)
SELECT '{"a": 1, "b": 2}'::jsonb || '{"b": 3, "c": 4}'::jsonb;
-- Output: {"a": 1, "b": 3, "c": 4}
-- The keys of the second one overwrite those of the first

Useful for "add/update fields without rewriting everything":

-- Add 'last_login' to the payload without touching anything else
UPDATE users
SET data = data || '{"last_login": "2026-05-02T10:00:00Z"}'::jsonb
WHERE id = 42;

Trap: the merge is not recursive. If you need a deep merge (blending nested objects), it doesn't do it:

SELECT '{"meta": {"a": 1, "b": 2}}'::jsonb || '{"meta": {"b": 3}}'::jsonb;
-- Output: {"meta": {"b": 3}}
-- It replaced the whole "meta" object, it didn't merge inside

For a deep merge you need jsonb_set or extra logic (capsule 04 explores more).

- (delete a key or an index)

-- Delete a key
SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b';
-- Output: {"a": 1, "c": 3}

-- Delete several keys
SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - ARRAY['a', 'c'];
-- Output: {"b": 2}

-- Delete an array element by index
SELECT '["x", "y", "z"]'::jsonb - 1;
-- Output: ["x", "z"]

#- (delete by path)

SELECT '{"a": {"b": {"c": 1, "d": 2}}}'::jsonb #- '{a,b,c}';
-- Output: {"a": {"b": {"d": 2}}}

jsonb_set (modify a field at a path)

-- Modify a nested value
SELECT jsonb_set(
  '{"a": {"b": 1}}'::jsonb,
  '{a,b}',
  '99'::jsonb
);
-- Output: {"a": {"b": 99}}

-- Insert if it doesn't exist (4th argument, defaults to true)
SELECT jsonb_set(
  '{"a": 1}'::jsonb,
  '{b}',
  '"hello"'::jsonb,
  true   -- create_if_missing
);
-- Output: {"a": 1, "b": "hello"}

jsonb_set is the tool when you need targeted updates without rewriting the whole JSONB. Useful but verbose — in SQLAlchemy (module 2) there are helpers that wrap it.

jsonb_insert (insert while respecting position in arrays)

jsonb_set replaces existing values; jsonb_insert inserts new ones at a specific array position, without replacing.

SELECT jsonb_insert(
  '["a", "c"]'::jsonb,
  '{1}',          -- insert at position 1
  '"b"'::jsonb
);
-- Output: ["a", "b", "c"]

Worked example: an end-to-end query with all three families

Let's go to a complete case. We want: "list all purchase events with an amount > 100 USD, adding a computed field with the country in uppercase."

Setup

DROP TABLE IF EXISTS events;
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO events (payload) VALUES
('{"action": "purchase", "amount": 150.00, "country": "us", "user_id": 42}'),
('{"action": "view",     "amount": 0,      "country": "mx", "user_id": 17}'),
('{"action": "purchase", "amount": 50.00,  "country": "ar", "user_id": 88}'),
('{"action": "purchase", "amount": 250.00, "country": "br", "user_id": 12}'),
('{"action": "click",    "amount": 0,      "country": "us", "user_id": 5}');

A query with all three families

SELECT
  id,
  -- ACCESS: extract fields
  payload->>'action'                       AS action,
  (payload->>'amount')::numeric            AS amount,
  payload->>'country'                      AS country,
  payload->>'user_id'                      AS user_id,

  -- MANIPULATION: add a computed field
  payload || jsonb_build_object(
    'country_upper', upper(payload->>'country')
  )                                        AS payload_extended

FROM events
-- SEARCH: filter by containment
WHERE payload @> '{"action": "purchase"}'
  AND (payload->>'amount')::numeric > 100;

Expected output:

 id | action  | amount | country | user_id | payload_extended
----+---------+--------+---------+---------+--------------------------------------------------
  1 | purchase| 150.00 | us      | 42      | {"action": "purchase", ..., "country_upper": "US"}
  4 | purchase| 250.00 | br      | 12      | {"action": "purchase", ..., "country_upper": "BR"}

What's happening:

1. Access (->>): we extract fields into SQL columns. Note the ::numeric cast so that > 100 is a numeric comparison, not a lexicographic one.
2. Search (@>): we filter by action: purchase. This is what GIN will speed up (capsule 05).
3. Manipulation (|| + jsonb_build_object): we add a computed field without rewriting the whole JSONB.

That structure — extract + filter + manipulate — is the pattern you'll repeat in every serious JSONB query.

Why does this matter in real work?

1. Telling indexable queries apart from non-indexable ones.

In a code review, someone writes WHERE data->>'status' = 'active' and adds a GIN index "to speed it up." You know that GIN isn't going to be used — it's an access operator, not a search one. It needs an expression index. The review prevents a wrong decision that gets discovered in production.

2. Rewriting queries to make them indexable.

A query that says WHERE data->>'status' = 'active' AND data->>'country' = 'US' isn't sped up by GIN. Rewritten as WHERE data @> '{"status": "active", "country": "US"}' it is. Same result, radically different performance. Knowing that equivalence saves you big refactors.

3. Avoiding latent bugs with casts.

(payload->>'age') > '25' gives you weird results because it compares strings. (payload->>'age')::int > 25 is the correct form. A bug that shows up "sometimes" and breaks on edge cases. Knowing how to cast from day one avoids the ticket.

4. Telling "key with null" apart from "nonexistent key."

A real case: an external API sends {"refund_id": null} when there's no refund. A query with data->>'refund_id' IS NOT NULL filters wrong — it discards both "there's no refund" and "refund explicitly null." With data ? 'refund_id' you can tell them apart. It shows up in API integrations and it's a source of subtle bugs.

Traps and common mistakes

Mistake 1 (conceptual): confusing -> with ->>

Symptom: comparing data->'name' = 'Ana' and getting false even though the name is Ana.

Why it happens: data->'name' returns '"Ana"'::jsonb (with quotes, of type jsonb). You're comparing jsonb with a string literal — they're not typographically equal.

How to detect it: run the query and look at the output with ->. If you see double quotes around the string, it's jsonb.

How to fix it: use ->> when you're going to compare against strings or return to the app: data->>'name' = 'Ana'.

Mistake 2 (conceptual): assuming that WHERE data->>'field' = 'x' uses GIN

Symptom: you created a GIN index on data and the query is still slow. EXPLAIN shows Seq Scan.

Why it happens: GIN only speeds up search operators (@>, ?, etc.). Access operators don't benefit from GIN. You need an expression index on the specific expression.

How to fix it:

-- Option A: rewrite the query with @> (preferred if applicable)
SELECT * FROM events WHERE payload @> '{"field": "x"}';

-- Option B: create an expression index for the original query
CREATE INDEX ON events ((payload->>'field'));

Mistake 3 (practical): numeric comparison without a cast

Symptom: WHERE data->>'price' > '100' returns strange results because '100' > '99' is false for strings ('100' < '99' lexicographically).

Why it happens: ->> returns text. Without a cast, the > comparison is lexicographic.

How to fix it: WHERE (data->>'price')::numeric > 100. And to index it: CREATE INDEX ON my_table (((data->>'price')::numeric)).

Mistake 4 (conceptual): using ? for "key with value X"

Symptom: WHERE data ? 'active' = true doesn't compile or doesn't return what you expected.

Why it happens: ? only tests the existence of the key, not its value. For "key with value X," use @> or ->>:

-- does the key 'active' exist?
WHERE data ? 'active'

-- does the key 'active' have the value true?
WHERE data @> '{"active": true}'
WHERE (data->>'active')::boolean = true

Mistake 5 (practical): non-recursive merge with ||

Symptom: data || '{"meta": {"new_key": 1}}' erases the rest of the keys inside meta.

Why it happens: || is a shallow merge: the meta key of the second object completely replaces the one from the first.

How to fix it: use jsonb_set or build the merge by hand:

UPDATE users
SET data = jsonb_set(
  data,
  '{meta}',
  (data->'meta') || '{"new_key": 1}'::jsonb
)
WHERE id = 42;

Mistake 6 (practical): operator order when chaining

Symptom: data->'a'->>'b' throws an error or a weird result.

Why it happens: ->> closes the chain (it returns text, you can't keep chaining jsonb operators). The typical error is continuing with ->>'c' after a ->>.

How to fix it: use -> in the middle and ->> only at the end, or use #>> with a path:

-- Wrong:
data->>'a'->>'b'   -- error

-- Right:
data->'a'->>'b'    -- jsonb, jsonb, text at the end
data#>>'{a,b}'     -- equivalent and cleaner

Exercises

Exercise 1: query equivalences

The following three queries should return the same result. Which one uses GIN if you have CREATE INDEX ON events USING gin(payload)? Why?

-- A
SELECT * FROM events WHERE payload->>'action' = 'purchase';

-- B
SELECT * FROM events WHERE payload @> '{"action": "purchase"}';

-- C
SELECT * FROM events WHERE payload ? 'action';

EXPLAIN ANALYZE SELECT * FROM events WHERE payload->>'action' = 'purchase';
-- Seq Scan

EXPLAIN ANALYZE SELECT * FROM events WHERE payload @> '{"action": "purchase"}';
-- Bitmap Heap Scan + Bitmap Index Scan on events_payload_idx

Exercise 2: deep extraction

Given the JSON:

SELECT '{
  "user": {
    "profile": {
      "social": {
        "twitter": "@example",
        "github": "exampleuser"
      }
    }
  }
}'::jsonb AS data;

Write two different ways to extract the Twitter handle as text. Say which one you prefer and why.

WITH t AS (SELECT '{"user":{"profile":{"social":{"twitter":"@example","github":"exampleuser"}}}}'::jsonb AS data)
SELECT
  -- Form A: chain -> and close with ->>
  data->'user'->'profile'->'social'->>'twitter' AS form_a,
  -- Form B: direct path
  data#>>'{user,profile,social,twitter}' AS form_b
FROM t;

-- Output:
--  form_a   |  form_b
-- ----------+-----------
--  @example |  @example

Exercise 3: distinguishing null from absent

Given:

INSERT INTO demo (data) VALUES
('{"refund_id": "ref_123"}'::jsonb),    -- id 2: has a refund
('{"refund_id": null}'::jsonb),         -- id 3: explicitly no refund
('{}'::jsonb);                           -- id 4: unknown (key absent)

Write queries that return:

a) The rows that explicitly marked "no refund" (key present, null value). b) The rows that have no information about a refund (key absent). c) The rows that have a refund (key present, non-null value).

-- a) Key present, null value
SELECT id FROM demo
WHERE data ? 'refund_id'
  AND data->>'refund_id' IS NULL;
-- Returns: 3

-- b) Key absent
SELECT id FROM demo
WHERE NOT (data ? 'refund_id');
-- Returns: 4

-- c) Key present with a non-null value
SELECT id FROM demo
WHERE data ? 'refund_id'
  AND data->>'refund_id' IS NOT NULL;
-- Returns: 2

SELECT id FROM demo WHERE data @> '{"refund_id": null}'::jsonb;
-- Returns: 3 (it also uses GIN!)

Exercise 4: rewriting to use GIN

The following query is slow and doesn't use the existing GIN on payload:

SELECT id, payload->>'amount' AS amount
FROM events
WHERE payload->>'action' = 'purchase'
  AND payload->>'country' = 'US'
  AND payload->>'currency' = 'USD';

Rewrite it so that it does use GIN. Validate the difference with EXPLAIN.

SELECT id, payload->>'amount' AS amount
FROM events
WHERE payload @> '{"action": "purchase", "country": "US", "currency": "USD"}';

EXPLAIN ANALYZE
SELECT id, payload->>'amount'
FROM events
WHERE payload->>'action' = 'purchase'
  AND payload->>'country' = 'US';
-- Seq Scan (slow if the table is large)

EXPLAIN ANALYZE
SELECT id, payload->>'amount'
FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}';
-- Bitmap Heap Scan
--   ->  Bitmap Index Scan on events_payload_idx

SELECT * FROM events
WHERE payload @> '{"action": "purchase", "country": "US"}'
  AND (payload->>'amount')::numeric > 100;
-- GIN filters by @>, post-filter in memory by amount.

Exercise 5: adding a computed field without rewriting

You have a users table with a data JSONB column. You want to add a last_seen_at field with a current timestamp to all users that have data ? 'active' with the value true. Without touching the other keys.

UPDATE users
SET data = data || jsonb_build_object('last_seen_at', now())
WHERE data @> '{"active": true}';

UPDATE users
SET data = jsonb_set(data, '{meta,last_seen_at}', to_jsonb(now()))
WHERE data @> '{"active": true}';

UPDATE users
SET data = jsonb_set(
  CASE WHEN data ? 'meta' THEN data ELSE data || '{"meta":{}}'::jsonb END,
  '{meta,last_seen_at}',
  to_jsonb(now())
)
WHERE data @> '{"active": true}';

Exercise 6: detecting the right operator

For each business question, say which operator to use (even if you can't write the exact query without more context):

a) "List users whose profile contains the tag 'admin'." b) "List users who have at least one of these roles: 'admin', 'editor', 'reviewer'." c) "Return the 'phone' field of each user, if it exists." d) "List users whose metadata contains exactly {"verified": true, "premium": true}." e) "Remove the temp_token field from a user's data after login." f) "Modify a user's internal score without touching the rest of the JSONB."

Summary and next step

In this capsule you learned the complete map of JSONB operators:

• Access (->, ->>, #>, #>>): they extract a value. NOT sped up by GIN. To index them, use expression indexes.
• Search (@>, <@, ?, ?|, ?&): they return a boolean. They ARE sped up by GIN. @> is the most used one in production.
• Manipulation (||, -, jsonb_set, jsonb_insert): they return a modified jsonb. Not indexed (they're writes).
• Key difference: -> returns jsonb, ->> returns text. Cast explicitly when you're going to compare numbers.
• Key difference: ? tests key existence; @> tests key+value containment. They aren't interchangeable.
• Rewriting access queries as search queries (->> + AND → @> with an object) is a free optimization when there's a GIN.

Before moving on you should be able to:

• Say from memory which operator returns which type and which family it belongs to
• Rewrite a query with three ->> + AND into a single @> and predict the improvement with EXPLAIN
• Distinguish "key with a null value" vs "nonexistent key" using ?, @>, and ->>
• Know why a WHERE data->>'field' = 'x' doesn't use GIN even though one exists

Next capsule — JSONB Path queries. The search operators you learned work for simple queries ("contains this," "has this key"). When you need to express "has an element in this array where X meets condition Y" or "extract all the values that match this predicate," the basic operators become unreadable. PostgreSQL 12+ brings JSON Path (jsonb_path_query, @@, @?) — the modern equivalent of XPath for JSON. Different syntax, much more expressive.

Resources

1. PostgreSQL 16 Documentation — JSON Functions and Operators — the complete official reference with all the operators and examples. Essential.
2. PostgreSQL 16 Documentation — JSONB Containment and Existence — the semantic detail of @>, <@, ? with edge cases.
3. Bruce Momjian — "JSONB tricks" — a post from the core team with practical cases.
4. pganalyze — "Working with JSONB in PostgreSQL" — an applied analysis of operators and real patterns.
5. Hussein Nasser — "PostgreSQL JSONB Operators Explained" — a video walkthrough of the operators with examples. 25 min.
6. PostgreSQL Wiki — JSONB Cheatsheet — a quick compilation to keep next to your editor.
7. Crunchy Data — "Indexing JSONB" — a preview of capsule 05, it contextualizes which operators are indexable.

Module 1 — Advanced PostgreSQL for Backend Guide

Next capsule: JSONB Path queries — advanced syntax for queries that the basic operators don't express cleanly.