JSONB: Flexible Payloads Without Losing Structure

Store semi-structured metadata in a jsonb column and query it with path operators, containment, and GIN indexes

The tv_db stores episode-level metadata in a jsonb column. Each row might hold a verified flag, a source rank, or a nested ratings object — fields that vary across sources and would require dozens of nullable columns to model relationally.\n\nJSONB solves this without sacrificing queryability. PostgreSQL parses the JSON at write time, stores it in a binary format that supports GIN indexing, and exposes a family of operators that make JSON feel like a first-class citizen.\n\nThe core distinction: -> returns a jsonb value (you can chain further operators). ->>) returns a text value (ready for LIKE, casting, or comparison). Forgetting which one you have is the most common JSONB bug — the type error makes it obvious.

JSONB vs JSON vs a Normalised Column

Use jsonb when:

The shape of the data varies across rows (e.g. different external API payloads)
You do not need to JOIN on every field
The column is queried by value containment, not equality on a known key every time

jsonb stores a parsed binary representation; json stores a raw text copy. Always use jsonb — it is indexable and faster to query.

Reading Keys with -> and ->>

-- raw_payload = '{"source_rank": 42, "verified": true}'
SELECT
    raw_payload -> 'source_rank'   AS rank_jsonb,    -- returns jsonb: 42
    raw_payload ->> 'source_rank'  AS rank_text,     -- returns text:  "42"
    raw_payload -> 'verified'      AS verified_jsonb  -- returns jsonb: true
FROM external_ratings_import
LIMIT 5;

Cast the text result to a numeric type when needed:

(raw_payload ->> 'source_rank')::int AS rank_int

Nested Paths with #> and #>>

-- payload = '{"meta": {"score": 8.5, "votes": 200}}'
SELECT raw_payload #>> '{meta,score}' AS nested_score
FROM external_ratings_import;

Containment: @>

The @> operator returns rows where the jsonb column contains the right-hand value. This is the correct operator for GIN-indexed JSONB lookups.

-- Find all rows where verified = true
SELECT id, source, raw_payload
FROM external_ratings_import
WHERE raw_payload @> '{"verified": true}';

GIN Index for Fast JSONB Lookups

CREATE INDEX idx_eri_payload ON external_ratings_import USING gin (raw_payload);

A GIN index covers all keys and values in the jsonb column. @> containment queries use this index — plain = equality on ->> does NOT.

Constructing and Modifying JSONB

-- Build a jsonb object from scratch
SELECT jsonb_build_object('source_rank', 99, 'verified', false);

-- Update one key in an existing jsonb column (returns a new value — does not mutate in place without UPDATE)
SELECT jsonb_set(raw_payload, '{source_rank}', '99') FROM external_ratings_import LIMIT 1;

jsonb

A PostgreSQL column type that stores JSON as a parsed binary structure. Unlike the json type (which stores raw text), jsonb deduplicates keys, normalises whitespace, and supports GIN indexing. Queries against jsonb can use index scans rather than sequential JSON text parsing.

-> vs ->>

The -> operator extracts a key and returns the result as jsonb — you can chain further operators on it. The ->> operator extracts a key and returns the result as plain text — ready for string operations, casting, or comparison. Type errors (e.g. comparing jsonb to text) are the most common JSONB bug; check which one you need before comparing.

@> containment

The @> operator tests whether the left-hand jsonb value contains the right-hand jsonb value as a subset. It is the idiomatic way to filter jsonb columns because it uses GIN indexes, making it fast at scale. Unlike ->> equality checks, containment works across nested keys and arrays.

🔌 Connect to tv_db

Connect to tv_db as the tv_db user. The JSONB exercises use the external_ratings_import table which has a raw_payload jsonb column containing source_rank and verified fields.

psql -U tv_db -d tv_db

psql (18.4) Type "help" for help. tv_db=>

🔍 Inspect the JSONB Column

Select a sample of rows from external_ratings_import showing the raw_payload column. Use LIMIT 5 to see what the JSON looks like.

SELECT id, source, raw_payload FROM external_ratings_import LIMIT 5;

id | source | raw_payload -------------------------------------+-----------------+--------------------------------------- ... | IMDb | {"verified": false, "source_rank": 0} ... | Rotten Tomatoes | {"verified": false, "source_rank": 1} ... | Metacritic | {"verified": false, "source_rank": 2} ... | TMDB | {"verified": true, "source_rank": 3} ... | Trakt | {"verified": false, "source_rank": 4} (5 rows)

➡️ Extract a Key with -> and ->>

Extract the source_rank field using both -> (returns jsonb) and ->> (returns text). Include both in the same SELECT so you can see the difference in the output column types.

SELECT source, raw_payload -> 'source_rank' AS rank_jsonb, raw_payload ->> 'source_rank' AS rank_text, (raw_payload ->> 'source_rank')::int AS rank_int FROM external_ratings_import LIMIT 5;

source | rank_jsonb | rank_text | rank_int -----------------+------------+-----------+---------- IMDb | 0 | 0 | 0 Rotten Tomatoes | 1 | 1 | 1 Metacritic | 2 | 2 | 2 TMDB | 3 | 3 | 3 Trakt | 4 | 4 | 4 (5 rows)

🎯 Filter with @> Containment

Find all rows where the payload has verified set to true. Use the @> containment operator — this is the idiomatic, index-friendly way to match jsonb values.

SELECT id, source, score, raw_payload FROM external_ratings_import WHERE raw_payload @> '{"verified": true}' LIMIT 5;

id | source | score | raw_payload -------------------------------------+----------+-------+--------------------------------------- ... | TMDB | 4.12 | {"verified": true, "source_rank": 3} ... | IMDb | 4.22 | {"verified": true, "source_rank": 8} ... | TMDB | 5.62 | {"verified": true, "source_rank": 13} ... | IMDb | 7.52 | {"verified": true, "source_rank": 18} ... | Metacritic| 8.32 | {"verified": true, "source_rank": 23} (5 rows)

📊 Aggregate Inside JSONB

Count rows and compute the average score grouped by the verified field inside the payload. Cast the ->> result to boolean to group by it properly.

SELECT (raw_payload ->> 'verified')::boolean AS verified, COUNT(*) AS row_count, ROUND(AVG(score), 2) AS avg_score FROM external_ratings_import GROUP BY 1 ORDER BY 1;

verified | row_count | avg_score ----------+-----------+----------- f | 333 | 5.44 t | 167 | 5.53 (2 rows)

🔧 Update a JSONB Field with jsonb_set

Use jsonb_set to update the source_rank to 0 for all rows from Trakt (simulating a re-ranking operation). Use RETURNING to confirm the change.

UPDATE external_ratings_import SET raw_payload = jsonb_set(raw_payload, '{source_rank}', '0') WHERE source = 'Trakt' RETURNING source, raw_payload;

source | raw_payload --------+--------------------------------------- Trakt | {"verified": false, "source_rank": 0} Trakt | {"verified": true, "source_rank": 0} Trakt | {"verified": false, "source_rank": 0} ... (100 rows) UPDATE 100

Lab complete! You can now query and update JSONB columns:\n\n\n col->'key' : ✅ extract jsonb child\n col->>'key' : ✅ extract text child (cast as needed)\n col @> '{...}' : ✅ containment filter (GIN-indexed)\n jsonb_set(col,path) : ✅ update one key without rewriting the whole payload\n GIN index : ✅ makes containment queries fast\n

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