JSONB: Documents in PostgreSQL

100,000 event payloads stored as JSONB. Query inside them, extract nested values, and index them — all without ever normalizing them into columns.

JSONB stores a parsed, binary representation of a JSON document, which makes it genuinely queryable rather than just a text blob to be pulled out whole and parsed in application code. The two navigation operators are the foundation: -> returns a nested value as JSONB, keeping it navigable for further chaining, while ->> returns that same value as text, ready to compare against a plain string. @> tests whether one JSONB value contains another as a subset — the natural operator for "does this document have this field set to this value" — while ? tests only for a key's existence, regardless of its value.\n\nFor genuinely nested structures — an array of line items inside an order, say — jsonb_path_query runs a full SQL/JSON path expression against the document, capable of filtering inside a nested array directly, which plain -> chaining alone cannot express. And because none of this changes how the data is physically stored, a GIN index using jsonb_path_ops is what actually makes @> containment queries fast at scale — the exact same indexing mechanism this certificate's Index Engineering block already proved for full-text search.

Navigation: -> for JSONB, ->> for Text

SELECT count(*) FROM sql_events WHERE payload->>'type' = 'checkout';
-- 25000

SELECT id, payload->'user'->>'email' AS email FROM sql_events WHERE id IN (1,2,3);

 id |       email
----+-------------------
  1 | user1@example.com
  2 | user2@example.com

payload->'user' returns the nested user object as JSONB, still chainable; ->>'email' on the end converts the final step to plain text for comparison or display.

? for Key Existence, Independent of Value

SELECT count(*) FROM sql_events WHERE payload ? 'type';           -- 100000
SELECT count(*) FROM sql_events WHERE payload ? 'discount_code';  --      0

Every event has a type key (regardless of what it's set to); none has a discount_code key at all — ? answers a structural question about the document's shape, not about any value inside it.

jsonb_path_query: Filtering Inside a Nested Array

SELECT id, jsonb_path_query(payload, '$.items[*] ? (@.price > 15)')
FROM sql_events WHERE id <= 20 ORDER BY id;

 id |       jsonb_path_query
----+-------------------------------
 11 | {"sku": "SKU11", "price": 16}
 12 | {"sku": "SKU12", "price": 17}

$.items[*] ? (@.price > 15) walks into the items array and returns only the elements whose price field exceeds 15 — a filter inside a nested array, which plain -> chaining has no way to express at all.

@>, Before and After a GIN Index

EXPLAIN SELECT count(*) FROM sql_events WHERE payload @> '{"type":"checkout"}';

Aggregate  (cost=3484.61..3484.62 rows=1 width=8)
  ->  Seq Scan on sql_events  (cost=0.00..3424.00 rows=24242 width=0)

CREATE INDEX ON sql_events USING GIN(payload jsonb_path_ops);
EXPLAIN SELECT count(*) FROM sql_events WHERE payload @> '{"type":"checkout"}';

Aggregate  (cost=2714.99..2715.00 rows=1 width=8)
  ->  Bitmap Heap Scan on sql_events  (cost=177.36..2654.39 rows=24242 width=0)
        ->  Bitmap Index Scan on sql_events_payload_idx

The identical query, before and after one index — Seq Scan becomes a genuine Bitmap Heap Scan the moment a GIN index built specifically for @> exists.

-> vs ->>

Both navigate one level into a JSONB value by key or array index. -> returns the result as JSONB, preserving its type and letting further -> or ->> operations chain onto it; ->> returns the result as text, which is what a plain string comparison (= 'checkout') needs. Chaining several -> steps and finishing with a single ->> at the end is the standard pattern for extracting a deeply nested scalar value as text.

jsonb_path_ops

A GIN operator class specifically optimized for the @> containment operator on JSONB, producing a smaller and faster index than the default jsonb_ops class at the cost of not supporting the ?, ?|, and ?& key-existence operators — the identical size-versus-operator-coverage tradeoff already seen for JSONB GIN indexing back in Block 3.2, applied here to a genuinely different dataset and use case.

🧭 Navigate Nested JSONB With -> and ->>

Extract a top-level field as text for comparison, then navigate two levels deep to a nested field.

psql -U postgres -d beer_db -c "SELECT count(*) FROM sql_events WHERE payload->>'type' = 'checkout';"

psql -U postgres -d beer_db -c "SELECT id, payload->'user'->>'email' AS email FROM sql_events WHERE id IN (1,2,3);"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM sql_events WHERE payload->>'type' = 'checkout';" SET count ------- 25000 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT id, payload->'user'->>'email' AS email FROM sql_events WHERE id IN (1,2,3);" SET id | email ----+------------------- 1 | user1@example.com 2 | user2@example.com 3 | user3@example.com (3 rows)

🔑 Test Key Existence With ?, Independent of Value

Check whether a key exists on the document at all, contrasting a key that is always present with one that never appears.

psql -U postgres -d beer_db -c "SELECT count(*) FROM sql_events WHERE payload ? 'type';" -c "SELECT count(*) FROM sql_events WHERE payload ? 'discount_code';"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM sql_events WHERE payload ? 'type';" -c "SELECT count(*) FROM sql_events WHERE payload ? 'discount_code';" SET count -------- 100000 (1 row) count ------- 0 (1 row)

🔍 Filter Inside a Nested Array With jsonb_path_query

Find only the line items inside each event's nested items array whose price exceeds 15.

psql -U postgres -d beer_db -c "SELECT id, jsonb_path_query(payload, '\$.items[*] ? (@.price > 15)') FROM sql_events WHERE id <= 20 ORDER BY id;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT id, jsonb_path_query(payload, '\$.items[*] ? (@.price > 15)') FROM sql_events WHERE id <= 20 ORDER BY id;" SET id | jsonb_path_query ----+------------------------------- 11 | {"sku": "SKU11", "price": 16} 12 | {"sku": "SKU12", "price": 17} 13 | {"sku": "SKU13", "price": 18} 14 | {"sku": "SKU14", "price": 19} 15 | {"sku": "SKU15", "price": 20} 16 | {"sku": "SKU16", "price": 21} 17 | {"sku": "SKU17", "price": 22} 18 | {"sku": "SKU18", "price": 23} 19 | {"sku": "SKU19", "price": 24} 20 | {"sku": "SKU0", "price": 25} (10 rows)

⚡ Index @> Containment With GIN and Confirm the Switch

Run a containment query before and after adding a GIN jsonb_path_ops index, confirming the plan genuinely changes.

psql -U postgres -d beer_db -c "EXPLAIN SELECT count(*) FROM sql_events WHERE payload @> '{\"type\":\"checkout\"}';"

psql -U postgres -d beer_db -c "CREATE INDEX ON sql_events USING GIN(payload jsonb_path_ops);"

psql -U postgres -d beer_db -c "EXPLAIN SELECT count(*) FROM sql_events WHERE payload @> '{\"type\":\"checkout\"}';"

student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT count(*) FROM sql_events WHERE payload @> '{\"type\":\"checkout\"}';" SET QUERY PLAN ----------------------------------------------------------------------- Aggregate (cost=3484.61..3484.62 rows=1 width=8) -> Seq Scan on sql_events (cost=0.00..3424.00 rows=24242 width=0) Filter: (payload @> '{"type": "checkout"}'::jsonb) (3 rows) student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX ON sql_events USING GIN(payload jsonb_path_ops);" SET CREATE INDEX student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT count(*) FROM sql_events WHERE payload @> '{\"type\":\"checkout\"}';" SET QUERY PLAN ------------------------------------------------------------------------------------------------- Aggregate (cost=2714.99..2715.00 rows=1 width=8) -> Bitmap Heap Scan on sql_events (cost=177.36..2654.39 rows=24242 width=0) Recheck Cond: (payload @> '{"type": "checkout"}'::jsonb) -> Bitmap Index Scan on sql_events_payload_idx (cost=0.00..171.30 rows=24242 width=0) Index Cond: (payload @> '{"type": "checkout"}'::jsonb) (5 rows)

Lab 3.3.4 complete. JSONB as a genuinely queryable, genuinely indexable document store:\n\n\n -> / ->> navigation, nested : ✅ 25,000 checkout events, emails extracted\n ? key existence, independent value : ✅ 100,000 vs 0, structural check confirmed\n jsonb_path_query, nested array filter: ✅ only matching items returned\n GIN jsonb_path_ops, @> containment : ✅ Seq Scan -> Bitmap Heap Scan, real\n

Enable JavaScript to run the live terminal and track your progress.