JSONB vs JSON vs TEXT: the mental model before the operators

Capsule overview

In PostgreSQL there are three ways to store JSON in a column: as text, as json, or as jsonb. Only one of the three is correct 99% of the time, but all three compile, all three accept JSON data, and all three look identical in a SELECT. The difference only comes to light when you try to index, filter, or read a field from the JSON with any non-trivial query. At that moment, one of the three is obviously wrong and you're going to be buried in a migration.

This capsule builds the mental model that will guide you through the rest of the module: what each type does internally, when to use which, and why jsonb almost always wins. Without this, the operators in capsule 03 are memorized commands. With this, the operators make sense: you'll understand why some can use GIN and others can't, why jsonb is slower to insert but much faster to query, and why storing JSON as text is an anti-pattern even though "it works."

By the end you'll be able to defend the column decision in an interview, you'll know how to spot in code review when someone declared the wrong type, and you'll have the mental model ready for the indexes coming in capsule 05.

The three options, one sentence each

If after reading this chapter you can't justify using something other than jsonb, don't use it. The default rule is jsonb always, with a justified exception when it applies.

Mental model: eager parser vs lazy parser

The central difference between json and jsonb is when PostgreSQL parses the JSON.

                  ┌─────────────────┐
INSERT with json  │  Stores the     │     SELECT of the field
                  │  string as-is   │  ───────────────────►   Parses every time
                  │  (1 ms)         │                          it is accessed
                  └─────────────────┘                          (10-100 ms on large payloads)


                  ┌─────────────────┐
INSERT with jsonb │  Parses now,    │     SELECT of the field
                  │  stores as      │  ───────────────────►   Reads binary directly
                  │  binary (3 ms)  │                          (0.1-1 ms)
                  └─────────────────┘

json is a lazy parser: it defers the cost of parsing until someone reads it. Every query that touches the JSON pays for the parsing. You insert cheap, you read expensive.

jsonb is an eager parser: it pays for the parsing once on insert and stores a structured binary representation. Every query that touches the JSON only reads binary. You insert expensive (a little), you read cheap (a lot).

In real backend applications, you read far more than you write — a typical ratio is 10 reads per 1 write. That alone justifies jsonb in almost every case.

And why is text here?

Because in real code you're going to find columns declared as body TEXT that store JSON. The historical reason is usually: "the ORM didn't support JSONB" (true in 2014, not now) or "we want it to be opaque to PostgreSQL" (rarely true).

The problem with text is that PostgreSQL doesn't understand it's JSON. It can't validate, it can't index, it can't use operators. To run any non-trivial query you need to cast: (body::jsonb)->>'field'. Every query pays for the parsing like lazy json, without any advantage over json and with the disadvantage that no field is validated on insert.

Rule: if you're going to store JSON, declare it as jsonb. If you have text with JSON inside, that's technical debt.

Internal differences — the detail that matters

1. jsonb deduplicates keys

-- Insert with two repeated keys
SELECT '{"a": 1, "a": 2}'::json;
-- Output: {"a": 1, "a": 2}
-- Keeps the duplicate, returns the string as it came in

SELECT '{"a": 1, "a": 2}'::jsonb;
-- Output: {"a": 2}
-- Keeps the last one, deduplicates while parsing

This matters because the standard JSON client (JavaScript, Python json.loads) deduplicates the same way: it keeps the last one. json preserves a technically valid behavior of the standard but one that is rarely useful. jsonb aligns with what your app expects.

2. jsonb does not preserve key order or whitespace

SELECT '{"b": 1, "a": 2}'::json;
-- Output: {"b": 1, "a": 2}  ← order preserved

SELECT '{"b": 1, "a": 2}'::jsonb;
-- Output: {"a": 2, "b": 1}  ← alphabetical order (or by internal hash)

jsonb reorganizes to optimize access. If you depend on the exact order (digital signatures over the raw string, legal contracts that require identical bytes), you need json or text. But those cases are real and specific exceptions, not the norm.

3. Only jsonb can be indexed with GIN

This is the decisive factor in any production app.

-- This works
CREATE INDEX ON my_table USING gin(jsonb_col);
CREATE INDEX ON my_table USING gin(jsonb_col jsonb_path_ops);

-- This does NOT work
CREATE INDEX ON my_table USING gin(json_col);
-- ERROR: data type json has no default operator class for access method "gin"

Without GIN, a query like WHERE payload @> '{"status": "active"}' over a json column is always a sequential scan. Over a jsonb column with GIN it's an index scan. The difference is hundreds to thousands of times on large tables — exactly what we'll explore in capsule 05.

4. Type-specific operators

json and jsonb share the access operators (->, ->>, #>, #>>). But the search operators that GIN speeds up are exclusive to jsonb:

If you choose json instead of jsonb, you give up half the language. And the operators you lose are exactly the ones a real app needs (search, not just reading).

Worked example: the real cost on an events table

Let's see with data what the theory says. We create two identical tables except for the type of the payload column, insert the same dataset, and compare behavior.

Setup

-- Create the test DB
-- In your shell: createdb jsonb_demo

-- Table with jsonb
DROP TABLE IF EXISTS events_jsonb;
CREATE TABLE events_jsonb (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

-- Table with json (lazy)
DROP TABLE IF EXISTS events_json;
CREATE TABLE events_json (
  id BIGSERIAL PRIMARY KEY,
  payload JSON NOT NULL
);

-- Table with text (untyped)
DROP TABLE IF EXISTS events_text;
CREATE TABLE events_text (
  id BIGSERIAL PRIMARY KEY,
  payload TEXT NOT NULL
);

Insert 100,000 synthetic events

-- Generate 100k varied payloads
INSERT INTO events_jsonb (payload)
SELECT jsonb_build_object(
    'action', (ARRAY['view', 'click', 'purchase', 'signup'])[1 + (random() * 3)::int],
    'country', (ARRAY['US', 'MX', 'ES', 'AR', 'BR'])[1 + (random() * 4)::int],
    'amount', round((random() * 500)::numeric, 2),
    'metadata', jsonb_build_object(
      'campaign', 'campaign-' || (random() * 10)::int,
      'referrer', (ARRAY['google', 'direct', 'twitter', 'email'])[1 + (random() * 3)::int]
    )
)
FROM generate_series(1, 100000);

-- Replicate the same data into json and text
INSERT INTO events_json (payload)
SELECT payload::text::json FROM events_jsonb;

INSERT INTO events_text (payload)
SELECT payload::text FROM events_jsonb;

Expected output:

INSERT 0 100000
INSERT 0 100000
INSERT 0 100000

Test storage size

SELECT
  'jsonb' AS type,
  pg_size_pretty(pg_total_relation_size('events_jsonb')) AS size
UNION ALL
SELECT
  'json',
  pg_size_pretty(pg_total_relation_size('events_json'))
UNION ALL
SELECT
  'text',
  pg_size_pretty(pg_total_relation_size('events_text'));

Typical output (varies a bit depending on hardware and the verbosity of the JSON):

 type  | size
-------+-------
 jsonb | 33 MB
 json  | 25 MB
 text  | 25 MB

jsonb takes up a bit more because it stores structural metadata (offsets, the type of each value). That's the cost you pay for being able to index and read fast. In any real app that extra 30% of storage is trivial compared to the benefit of queries that are 100x faster.

Test query speed

-- Timing on to measure
\timing on

-- On jsonb: the operator works and is comparatively fast
SELECT count(*) FROM events_jsonb
WHERE payload @> '{"action": "purchase", "country": "US"}';
-- Time: 25-80 ms (no index yet, but binary scan)

-- On json: @> doesn't even exist
SELECT count(*) FROM events_json
WHERE payload @> '{"action": "purchase", "country": "US"}';
-- ERROR: operator does not exist: json @> unknown

-- On json: you have to write it "by hand" with accessors
SELECT count(*) FROM events_json
WHERE payload->>'action' = 'purchase' AND payload->>'country' = 'US';
-- Time: 200-400 ms (parses every row on read)

-- On text: cast first
SELECT count(*) FROM events_text
WHERE (payload::jsonb) @> '{"action": "purchase", "country": "US"}';
-- Time: 800-1500 ms (parses every row + cast)

Reading the benchmark

And this is still without an index. In capsule 05 we're going to put GIN on jsonb and the query drops to milliseconds. The other two can't have GIN.

Immediate conclusion: jsonb isn't just "the modern one" — it's the only type that scales. json and text are traps with the appearance of equivalence.

When json (not jsonb) does make sense

There are two legitimate cases where json is the right choice. They're rare, but they exist.

Case 1: exact payload preservation

If you need to store the JSON byte-for-byte as it arrived (key order, whitespace, intentional duplicates), jsonb is going to normalize it for you. Some examples:

• Webhook signatures based on the raw body: Stripe, GitHub, and others sign the webhook body with HMAC. If you store the payload as jsonb and want to re-verify it, the altered order will break the signature.
• Legal contracts or regulated audits: "the exact data we received" can be a legal requirement.
• Forensic logs: "this is what the client sent us, unmodified."

In these cases: store the raw body as text with a clear name (raw_payload) and, if you also need the queryable version, add a second parsed_payload jsonb column. Don't use json as a compromise; make both needs explicit.

Case 2: write-only, never-query

If you have a column that is only written and read as an opaque block to show in a detail view (no filters, no searches, no aggregations), json saves you the eager parsing on insert. In practice this almost never applies because sooner or later someone is going to want to filter by something inside.

Operational rule: start with jsonb always. Only drop down to json or text if you have a documented case from the above. Converting jsonb → json afterwards is trivial; the other way around is too, but it requires re-parsing everything and validating.

The critical anti-pattern: using JSONB for relational data

This is the most important part of the capsule. JSONB is excellent for semi-structured data (flexible schema, optional properties, polymorphic). But abusing JSONB for data that is clearly relational is guaranteed technical debt.

The smell test

Ask yourself these questions before putting something in JSONB:

1. Am I going to JOIN with this data? If yes, it should be a table. JSONB with JOIN turns into unreadable queries that the planner doesn't optimize well.
2. Am I going to have foreign keys pointing at this? If yes, it should be a table. JSONB doesn't support FKs.
3. Is every record going to have exactly the same keys? If yes, they're columns. JSONB's flexibility contributes nothing — you pay the cost (storage, more expensive indexes) without the benefit.
4. Am I going to run aggregations (SUM, AVG, GROUP BY) over these fields in critical queries? If yes, they're columns. Aggregations over JSONB are possible but slower and less optimizable.
5. Do I need constraints on these fields (NOT NULL, CHECK, UNIQUE)? If yes, they're columns. JSONB has constraints but they're limited and verbose.

Example: the contrast

Anti-pattern: an order management system where each order has an items JSONB column with [{"product_id": 1, "qty": 3}, {"product_id": 5, "qty": 1}].

-- To list all orders that contain product_id=5
SELECT id FROM orders WHERE items @> '[{"product_id": 5}]';
-- It works, but: you can't JOIN with the products table, you can't FK,
-- you can't constrain UNIQUE(order_id, product_id), and aggregating
-- inventory per product requires unnesting the JSON.

Correct pattern: an order_items table with FKs to orders and products.

CREATE TABLE order_items (
  order_id BIGINT REFERENCES orders(id),
  product_id BIGINT REFERENCES products(id),
  qty INTEGER NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)
);

JOIN, FK, constraints, per-product aggregations — all trivial. No future problems. This is what JSONB should not be.

Example: the right place for JSONB

Imagine each order has variable metadata depending on the channel (web, mobile, partner API, B2B contract):

• Web: {"utm_source": "google", "session_id": "...", "device": "desktop"}
• Mobile: {"app_version": "3.2.1", "platform": "ios", "push_token": "..."}
• Partner API: {"partner_id": "ACME", "po_number": "123", "external_ref": "..."}

The keys are different per channel, you don't want to migrate the schema every time a new channel adds a field, you aren't going to JOIN with this metadata, you aren't going to SUM over it. This is legitimate JSONB.

ALTER TABLE orders ADD COLUMN metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

The difference is: relational domain data → table. Semi-structured auxiliary data → JSONB.

Why does this matter in real work?

1. Code reviews where someone declares body TEXT to store JSON.

The conversation is always the same: "we can change it later if we need to filter." Later is 6 months later with 20M rows and the migration costs weeks. You'll be able to argue with data: "if we leave it as text, we lose GIN, and the first non-trivial query will be a sequential scan. Change it to jsonb now — the migration later is expensive." You'll save weeks.

2. New schema design.

When you land on a greenfield project, you'll answer the question "does this go in columns or in JSONB?" with the smell test, not with instinct. The smell test saves you expensive refactors.

3. Diagnosing "JSONB is slow."

The complaint "JSONB is slow" usually comes from one of these: (a) there's no GIN index, (b) the column is declared as json and nobody noticed, (c) the query uses operators that GIN doesn't speed up. Knowing how to tell which one it is makes you the dev that ticket gets assigned to.

4. Senior technical interviews.

"When would you use PostgreSQL JSONB instead of MongoDB?" is a classic question. The expected answer is technical: key deduplication, eager parser, GIN, ACID transactions, 30% higher storage cost vs a pure document store, the relational-in-JSONB anti-pattern. If you answer "because I already have PostgreSQL in the stack," you came across as a junior.

Traps and common mistakes

Mistake 1 (conceptual): thinking json and jsonb are interchangeable

Symptom: "It doesn't matter which one I use, both store JSON."

Why it's wrong: they share an appearance (SELECT shows the same thing) but they diverge in available operators, indexability, and query cost. The lazy/eager parser is the central difference. json can't be indexed with GIN, doesn't support @>/?, parses on every access. They're different types with different use cases.

How to fix it: memorize the rule — jsonb by default, json only if you need exact preservation. If you chose json and can't justify it in one sentence, it was badly chosen.

Mistake 2 (conceptual): using JSONB for relational data

Symptom: a table with a JSONB column that contains an array of IDs from another table, queries that need to unnest the JSON to do "manual joins," slow aggregations.

Why it's wrong: JSONB doesn't support FKs, doesn't optimize well for JOINs, and aggregations are slower than over columns. You're re-implementing relationships without the tools PostgreSQL provides for relationships.

How to detect it: run the smell test. If you answer "yes" to the first two questions (am I going to JOIN?, am I going to have FKs?), it's relational, not JSONB.

How to fix it: model it as tables with FKs. JSONB is left only for legitimately semi-structured metadata.

Mistake 3 (practical): declaring text with JSON inside because "it's simpler"

Symptom: body TEXT or data TEXT columns that actually store JSON. The queries do (body::jsonb)->>'field' all over the place.

Why it happens: old ORMs didn't support jsonb (true in 2014), or the dev didn't know jsonb existed. The column remains as inherited technical debt.

How to detect it: search the code for ::jsonb or ::json applied to columns — it's the fingerprint of "this column should be jsonb but it's text/json."

How to fix it: migration with ALTER TABLE ... ALTER COLUMN ... TYPE jsonb USING <expression>. On large tables do it zero-downtime (technique covered in guide #13): new parallel column, dual write, backfill, switch.

Mistake 4 (conceptual): assuming jsonb doesn't preserve order and therefore "isn't good for JSON"

Symptom: "jsonb scrambles the keys, better to use json so the front end receives the original order."

Why it's wrong: the JSON client shouldn't depend on key order (it's an object, not an array). If your front end depends on the order, the front end is badly written. And if you need order as a requirement (case 1 of "when json does apply"), use a separate text column for the raw, but use jsonb for the queryable one.

How to fix it: document that key order isn't part of the contract. If it is part of the contract (HMAC signature over the string), store the raw separately.

Mistake 5 (practical): not validating the JSON when inserting into text columns

Symptom: a body TEXT column that stores JSON ends up with invalid strings in production (truncated, badly escaped, a mix of strings and JSON).

Why it happens: PostgreSQL doesn't validate anything in a text column. Any string gets in. jsonb validates on insert — invalid JSON is rejected.

How to fix it: another reason to use jsonb. Free validation on insert.

Exercises

Exercise 1: identify the correct type

For each case, decide whether the column should be text, json, or jsonb. Justify it in one sentence.

1. An API stores Stripe webhooks. The HMAC signature is computed over the raw body. You need to re-verify the signature 30 days later for an audit requirement.
2. A SaaS app stores "per-client configuration." Each client defines which keys they put in. You constantly filter by config @> '{"feature_x": true}' to list clients with that feature enabled.
3. An orders table needs to store the shipping address (street, city, zip, country) which is almost never queried — it's only printed on the shipping label.
4. A products table with a category field that is one of exactly 5 fixed values.
5. Audit logs that store the "before and after state" of a row before updating. They're queried occasionally to investigate incidents.

Exercise 2: compare storage and speed

Replicate the setup of the worked example (3 tables with jsonb, json, text) and insert 50,000 rows. Measure: (a) the size of each table with pg_total_relation_size, (b) the time of each equivalent query with \timing on. Report the numbers.

-- Setup
DROP TABLE IF EXISTS ex2_jsonb, ex2_json, ex2_text;
CREATE TABLE ex2_jsonb (id BIGSERIAL PRIMARY KEY, payload JSONB NOT NULL);
CREATE TABLE ex2_json  (id BIGSERIAL PRIMARY KEY, payload JSON NOT NULL);
CREATE TABLE ex2_text  (id BIGSERIAL PRIMARY KEY, payload TEXT NOT NULL);

-- Insert 50k
INSERT INTO ex2_jsonb (payload)
SELECT jsonb_build_object(
  'action', (ARRAY['view','click','purchase'])[1 + (random() * 2)::int],
  'country', (ARRAY['US','MX','ES'])[1 + (random() * 2)::int],
  'amount', round((random() * 200)::numeric, 2)
)
FROM generate_series(1, 50000);

INSERT INTO ex2_json (payload) SELECT payload::text::json FROM ex2_jsonb;
INSERT INTO ex2_text (payload) SELECT payload::text FROM ex2_jsonb;

-- Storage
SELECT
  'jsonb' AS t, pg_size_pretty(pg_total_relation_size('ex2_jsonb')) AS size
UNION ALL
SELECT 'json', pg_size_pretty(pg_total_relation_size('ex2_json'))
UNION ALL
SELECT 'text', pg_size_pretty(pg_total_relation_size('ex2_text'));

-- Example output:
--    t   |  size
-- -------+--------
--  jsonb | 13 MB
--  json  | 9 MB
--  text  | 9 MB

-- Speed
\timing on
SELECT count(*) FROM ex2_jsonb WHERE payload @> '{"country":"US","action":"purchase"}';
-- ~15-30 ms
SELECT count(*) FROM ex2_json  WHERE payload->>'country'='US' AND payload->>'action'='purchase';
-- ~80-150 ms
SELECT count(*) FROM ex2_text  WHERE (payload::jsonb) @> '{"country":"US","action":"purchase"}';
-- ~250-450 ms

Exercise 3: anti-pattern detector

The following schema showed up in a code review. Identify the problems and propose a refactor.

CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  data JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- Example row:
-- {
--   "email": "ana@example.com",
--   "phone": "+5491112345678",
--   "addresses": [
--     {"id": 1, "street": "...", "city": "..."},
--     {"id": 2, "street": "...", "city": "..."}
--   ],
--   "preferences": {"newsletter": true, "language": "es"}
-- }

CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email CITEXT UNIQUE NOT NULL,           -- column, indexed, unique
  phone TEXT,                              -- column
  preferences JSONB NOT NULL DEFAULT '{}'  -- stays as JSONB
);

CREATE TABLE addresses (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  street TEXT NOT NULL,
  city TEXT NOT NULL,
  is_default BOOLEAN DEFAULT FALSE
);

CREATE INDEX ON addresses(customer_id);

Exercise 4: defend a decision

Your team is discussing how to store the raw response from an external service (a payment provider) that sends JSON with 30+ fields per transaction. You use some fields in queries (status, amount, currency), others you never use but store "just in case" for auditing.

A colleague proposes: "let's store it all as text — it's the simplest and if we need something we'll parse it in Python." What do you argue?

Exercise 5: prediction

Without running it, predict what each query returns over a data JSONB column with the value '{"a": 1, "b": null}'::jsonb:

SELECT data->>'a';
SELECT data->>'b';
SELECT data->>'c';
SELECT data ? 'a';
SELECT data ? 'b';
SELECT data ? 'c';
SELECT data @> '{"b": null}';

Summary and next step

In this capsule you built the mental model:

• jsonb is an eager parser: it pays the cost on insert, indexes with GIN, deduplicates keys, supports search operators. The default you want 99% of the time.
• json is a lazy parser: cheap to insert, expensive to read, not indexable with GIN, doesn't support @> or ?. Only justifiable if you need byte-exact preservation of the payload.
• text with JSON inside is technical debt. No validation, no operators, no indexes. If you find it in inherited code, plan a migration.
• The critical anti-pattern is using JSONB for relational data that needs FKs, JOINs, constant aggregations, or constraints. Apply the smell test before declaring JSONB.
• Central difference: JSONB normalizes (order, duplicates); json preserves. That matters for HMAC over a raw body, not for your typical app.

Before moving on you should be able to:

• Justify choosing jsonb without saying "just because"
• Spot JSONB used for relational data in a code review
• Explain why json can't be indexed with GIN and what that implies
• Predict when ? and @> return true/false over payloads with null keys

Next capsule — Core JSONB operators. You already have the "what it is" and the "when to use it." Now we go to the "how you operate it": the ->, ->>, #>, #>> (access), @>, <@, ?, ?|, ?& (search), and ||, -, jsonb_set (manipulation) operators. You'll see which ones benefit from GIN (the search ones) and which don't (the access ones) — that knowledge is what will make capsules 05 and 06 obvious instead of magic.

Resources

1. PostgreSQL 16 Documentation — JSON Types — the official reference on json vs jsonb. Section 8.14.
2. PostgreSQL 16 Documentation — JSON Functions and Operators — the complete operator table with availability by type.
3. Bruce Momjian — "PostgreSQL JSON Capabilities" — official slides from the core team explaining the design of JSONB.
4. pganalyze — Lukas Fittl — "When to Use JSONB in Postgres" — an analysis of when JSONB is the right choice vs when it isn't.
5. PostgreSQL Wiki — JSONB — the historical wiki with the original justification for the type.
6. Tom Lane — pgsql-hackers thread on JSONB normalization — a technical discussion from the PostgreSQL committer about why JSONB normalizes (historical context).
7. Hussein Nasser — "JSON vs JSONB in PostgreSQL" — an accessible explanation with benchmarks.

Module 1 — Advanced PostgreSQL for Backend Guide

Next capsule: Core JSONB operators — the complete language of access, search, and manipulation.