JSONB in PostgreSQL

NoSQL and choosing a database Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Predict the output

What does the containment query return?

SQL · PostgreSQL 17
CREATE TABLE items (id int, attrs jsonb);
INSERT INTO items VALUES
  (1, '{"color": "red", "size": "M"}'),
  (2, '{"color": "blue"}'),
  (3, '{"color": "red"}');
SELECT id FROM items
WHERE attrs @> '{"color": "red"}'
ORDER BY id;

Options
Hint 1 of 1

Does @> require an exact match?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A 1 and 3

Correct. Both contain color red; extra keys don't matter.

@> asks whether the stored document contains the given one. Rows 1 and 3 both have color red; row 1's extra size key doesn't matter.

Why not the others?
  • B 3 only. Containment allows extra keys in the stored value.
  • C 1 only. Item 3 also has color red.
  • D All three. Item 2 is blue.
Result on PostgreSQL 17 (psql)
id
1
3

(2 rows)

30-second answer

Use a jsonb column: it stores parsed, binary JSON, supports containment (@>), key existence and path queries, and can be indexed with GIN. Keep fields you filter, join or constrain on as real columns, and the variable rest in JSONB. Limits: no foreign keys or per-key types inside the document, planner statistics are weaker, and updating one key rewrites the whole value.

Practise saying it · 30 s. No recording, just a timer

Explain JSONB usage.

Say your answer out loud, then check which of these points you covered:

  • Hybrid: columns + JSONB
  • @> and GIN
  • Expression indexes
  • No FKs inside
  • Whole-value rewrites

Deep dive

jsonb_path_ops GIN indexes are smaller and faster, but support only @> and the jsonpath operators @? and @@, not key-exists operators like ?.

Expression indexes on one key: CREATE INDEX ON items ((attrs->>'color')).

Generated columns can lift a hot key into a typed column.

Use json only if you must keep key order or duplicate keys exactly.

Index a JSONB key and query it Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
CREATE TABLE items (id int PRIMARY KEY, attrs jsonb NOT NULL);
INSERT INTO items
SELECT g, jsonb_build_object('color', (ARRAY['red','blue'])[g % 2 + 1])
FROM generate_series(1, 1000) g;
CREATE INDEX items_attrs ON items USING gin (attrs jsonb_path_ops);
SELECT count(*) FROM items WHERE attrs @> '{"color": "red"}';

Common mistakes

  • Putting everything in one JSONB column.
  • Filtering on a JSONB key with no index.
  • Storing numbers as strings inside JSONB and comparing them as text.

Senior take & follow-ups

Hybrid model, operators and GIN, and where real columns are still better.

Follow-up questions

Think of your answer first, then open the hint.

  1. json or jsonb?

    Show hint

    jsonb for nearly everything; it's indexable and faster to query.

  2. How do you validate JSONB?

    Show hint

    CHECK constraints with jsonb functions, or validate in the app.

Related questions

Suggest a fix if something here is wrong or out of date.