0Pricing
SQL Academy · Lesson

JSONB vs JSON: When to Use Each

Choose between JSON (text, preserves whitespace) and JSONB (binary, indexable, deduplicated keys) for the right storage.

JSONB vs JSON: When to Use Each is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Two JSON Types in PostgreSQL

  • JSON — stores the exact text you put in
  • JSONB — decomposed binary format, deduplicated keys, indexable

JSON: Text Storage

JSON preserves whitespace, key order, and duplicate keys. Reparse on every read. No GIN indexing. Almost never the right choice for new code.

INSERT INTO docs (data) VALUES ('{"a": 1, "a": 2}'::JSON);
SELECT data->'a' FROM docs;     -- whatever the parser picks

JSONB: Binary, Indexable

JSONB:

  • Stored decomposed → no reparse on read
  • Duplicate keys collapsed (last wins)
  • Key order not preserved
  • Supports GIN indexing
  • Supports rich path operators

JSONB Example

A simple events table:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  data JSONB NOT NULL
);

INSERT INTO events (data) VALUES
  ('{"type":"login","user_id":42,"ip":"1.2.3.4"}');

Path Operators

JSONB has rich operators (covered in the next lesson):

SELECT data->'user_id'   FROM events;       -- → JSONB
SELECT data->>'user_id'  FROM events;       -- → TEXT
SELECT data #>> '{user,name}' FROM events;  -- nested text path

Containment

The most useful JSONB operator: @> (contains).

SELECT * FROM events WHERE data @> '{"type":"login"}';

-- Combined with a GIN index, this is fast even on billions of rows.

Equality of JSONB

Two JSONB values are equal if their content matches — key order doesn't matter:

SELECT '{"a":1,"b":2}'::JSONB = '{"b":2,"a":1}'::JSONB;    -- TRUE

Casting Between Types

Cast freely:

SELECT '{"a":1}'::JSONB->'a';                -- 1 (as JSONB)
SELECT ('{"a":1}'::JSONB)->>'a';             -- '1' (as TEXT)
SELECT ('{"a":1}'::JSONB)->'a'::INT;          -- 1 (as INT after extraction)

When NOT to Use JSONB

If the data has a fixed shape, use real columns:

  • Faster queries (no document parsing per row)
  • Strong types and constraints
  • Smaller storage
  • Better statistics

JSONB shines when the shape varies or evolves rapidly.

Hybrid Modelling

Best practice: structured columns for stable fields, JSONB for variable extras:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL,
  event_type TEXT NOT NULL,
  user_id BIGINT,
  extra JSONB NOT NULL DEFAULT '{}'::JSONB
);

Validation

JSONB stores any valid JSON. For structured constraints, use CHECK constraints or schema enforcement at the app layer:

ALTER TABLE events
  ADD CONSTRAINT data_has_type CHECK (data ? 'type');

Recap

JSONB > JSON for nearly all production cases.

  • JSONB: binary, indexed, fast paths
  • JSON: text round-trip only
  • Use JSONB for variable shapes; real columns for fixed shapes

Quick Check

You're designing a new table to store user-uploaded JSON documents you'll query by content. JSON or JSONB?

Frequently asked questions

Is the “JSONB vs JSON: When to Use Each” lesson free?

Yes — the full text of “JSONB vs JSON: When to Use Each” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “JSONB vs JSON: When to Use Each”?

Choose between JSON (text, preserves whitespace) and JSONB (binary, indexable, deduplicated keys) for the right storage. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “JSONB vs JSON: When to Use Each” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. JSONB vs JSON: When to Use Each
  2. Path Operators: -> ->> @>
  3. Indexing JSONB with GIN
  4. Modelling: When JSONB Beats Normalisation
← Back to SQL Academy