Working with JSONB in PostgreSQL

JSONB is the feature that lets PostgreSQL store and query semi-structured data without giving up on being a relational database. It's genuinely useful and genuinely easy to misuse. Reach for it in the right places and you get flexibility exactly where you need it. Reach for it everywhere and you throw away the constraints, types, and query clarity that made you pick Postgres in the first place. This article covers how JSONB works and, just as important, when not to use it.

This is part 24 of the PostgreSQL Masterclass and the start of the advanced-features module, following MVCC and VACUUM.

JSON vs JSONB

Postgres has two JSON types. json stores the exact text you gave it, whitespace and duplicate keys included, and re-parses it on every query. jsonb stores a decomposed binary form: faster to query, supports indexing, removes duplicate keys, and doesn't preserve key order or formatting.

For almost everything, use jsonb. The only reason to pick json is if you need to store and return the exact original text verbatim, which is rare. Everywhere else, jsonb is the right default because it's the one you can index and query efficiently.

CREATE TABLE events (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  type    text NOT NULL,
  data    jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);

When JSONB is the right tool

The honest use cases share one trait: the data's shape is genuinely unknown or variable at design time.

• External payloads you want verbatim. A webhook body or an API response you store for audit or replay. You don't control its schema, so modeling it in columns is fruitless.
• User-defined custom fields. A form builder or a CRM where each customer defines their own fields. You can't have a column per possible field.
• Sparse, varied attributes. Product catalogs where a book has an author and a shirt has a size, and forcing both into one wide table means mostly-null columns.

In these cases JSONB earns its place, because the alternative is either an unmanageable number of columns or an entity-attribute-value mess that's worse.

When JSONB is the wrong tool

The anti-pattern is using JSONB for data whose shape you already know. If you're storing a user's email, name, and signup date in a JSONB blob, you've given up:

• Constraints. No NOT NULL, no foreign keys, no CHECK. Nothing stops a row from having a typo'd key or a missing field.
• Types. Everything is JSON text and numbers. A date is a string; a wrong type isn't caught.
• Clean queries and indexes. Every access needs JSON operators, and while you can index JSONB, plain columns are simpler and often faster.

The rule: model the known shape as real columns, and put only the genuinely variable part in a JSONB column. A hybrid table (structured columns for what you know, one JSONB column for the flexible extras) is usually the right design, not all-columns or all-JSONB.

Reading data out

JSONB has two families of access operators, and the difference trips people up. -> returns a JSONB value; ->> returns text. Use ->> when you want a plain string to compare or display:

SELECT data -> 'user' -> 'name'  AS name_jsonb,   -- still JSONB
       data -> 'user' ->> 'name' AS name_text     -- text
FROM events;

-- deep access with a path
SELECT data #>> '{user,address,city}' AS city FROM events;

#> and #>> take a path array to reach nested values in one step. When you filter on a JSONB field, remember it's text coming out, so cast if you need a number:

SELECT * FROM events
WHERE (data ->> 'amount')::numeric > 100;

The containment operator is the important one

The single most useful JSONB operator is @>, containment. It asks "does this JSONB contain this sub-object," and it's both expressive and indexable (the next article is all about indexing it):

-- rows whose data contains this key/value
SELECT * FROM events WHERE data @> '{"type": "signup"}';

-- nested containment works too
SELECT * FROM events WHERE data @> '{"user": {"plan": "pro"}}';

Containment is the query you'll write most, because it maps cleanly to "find rows where this JSON field equals this value" and, unlike ->> comparisons, a GIN index can accelerate it directly.

Updating JSONB

You can modify JSONB in place with jsonb_set and friends, though updating a nested field rewrites the whole value (MVCC still makes a new row version):

-- set data.user.plan = 'enterprise'
UPDATE events
SET data = jsonb_set(data, '{user,plan}', '"enterprise"')
WHERE id = 1;

-- merge in new keys with ||, remove a key with -
UPDATE events SET data = data || '{"verified": true}' WHERE id = 1;
UPDATE events SET data = data - 'verified' WHERE id = 1;

Frequent updates to large JSONB documents generate bloat, since each update rewrites the whole document. If a field changes often, that's a hint it might belong in its own column rather than buried in a blob.

JSONB is a sharp tool. Used for the genuinely schemaless parts of your data, it's exactly right and keeps you from bending your schema around unknowns. Used as a substitute for designing a schema, it quietly costs you everything a relational database is good at. Model what you know, JSONB what you don't, and you get the best of both.

Next, we make JSONB fast: the GIN indexes and operator classes that turn a containment query over millions of documents from a full scan into an instant lookup.