Published: 2026-04-14

PostgreSQL JSON and JSONB: The Complete Guide for SQL Developers

PostgreSQL has some of the most mature JSON support of any relational database. jsonb, GIN indexes, the SQL/JSON path language, and over a decade of operator evolution mean a single Postgres instance can do the work most teams used to split between Postgres and MongoDB. As of May 2026, PostgreSQL 18 is GA and ships every feature in this guide. Here's the practical guide.

Quick Answers

A Brief History of JSON in PostgreSQL

PostgreSQL's JSON story is older than most developers realize. The json type landed in 9.2 (2012), the binary jsonb type in 9.4 (2014), the SQL/JSON path language in 12 (2019), the IS JSON predicate and pg_input_is_valid in 16, and the standard SQL/JSON functions (JSON_TABLE, JSON_VALUE, JSON_QUERY, JSON_EXISTS) in 17 (2024). Among open-source relational databases, PostgreSQL's SQL/JSON coverage is now one of the most complete.

json vs jsonb — Pick One and Move On

PostgreSQL ships two JSON types. They look identical from SQL but behave very differently underneath:

• json stores the original text of the document verbatim. Whitespace, key order, and duplicate keys are preserved. Each query has to re-parse the text.
• jsonb parses the document at insert time and stores a binary tree. Whitespace is stripped, keys are sorted, and duplicates collapse to the last value. Queries skip parsing and can use indexes.

Use jsonb. The only reason to pick json is if your application depends on byte-for-byte preservation of an externally-signed document — a rare case. As of PG 17, the write-time cost of binary parsing is rarely measurable against the read-time savings on any non-trivial workload.

Querying JSON: Operators You Will Actually Use

PostgreSQL exposes JSON access through operators rather than function calls. The shortlist: See the PostgreSQL JSON functions and operators reference for the full list.

-- Extract a field as jsonb (chainable)
SELECT data->'address'->'city' FROM customers;

-- Extract as text (for comparisons in WHERE)
SELECT * FROM customers WHERE data->>'email' = '[email protected]';

-- Traverse a deep path
SELECT data #>> '{address,city}' FROM customers;

-- Containment: does the document contain this fragment?
SELECT * FROM orders WHERE items @> '[{"sku":"ABC-123"}]';

-- Key/path existence
SELECT * FROM customers WHERE data ? 'phone';
SELECT * FROM customers WHERE data @? '$.address.city';

-- SQL/JSON path predicate
SELECT * FROM products WHERE specs @@ '$.weight_kg > 5';

The mental model: -> keeps you in JSON-land, ->> drops you into SQL text-land. Almost every WHERE clause ends with ->>.

Indexing JSON Paths

This is where PostgreSQL leaves every other relational database behind. Three useful index strategies, all built on GIN indexes: (See jsonb Indexing in the Postgres docs.)

1. GIN on the whole document

Indexes every key and value. Best when you don't know which paths users will filter on:

CREATE INDEX idx_customers_data ON customers USING GIN (data);

2. GIN with jsonb_path_ops

Smaller and faster than the default operator class, but only supports the @> containment operator. Pick this when containment is your only access pattern:

CREATE INDEX idx_customers_data_ops ON customers
USING GIN (data jsonb_path_ops);

3. B-tree expression index on a known path

The fastest option when you have a single hot path. Behaves exactly like a normal column index:

CREATE INDEX idx_customers_email
  ON customers ((data->>'email'));

Validation, Generation, and Mutation

PostgreSQL exposes a full set of helpers that often surprise developers used to other engines:

• jsonb_set(doc, path, value) — immutable update; returns a new document
• doc - 'key' — remove a key
• doc || other_doc — concatenate / shallow-merge
• jsonb_pretty(doc) — format for humans
• jsonb_typeof(value) — runtime type check
• pg_input_is_valid(text, 'jsonb') — non-throwing validity check (16+)

PostgreSQL JSONB vs MongoDB

This is the question every team asks once they realize how capable jsonb is. The honest answer:

Pick PostgreSQL when your data has any relational structure at all, you want one database to operate, or you need real cross-document transactions and joins.

Pick MongoDB when your collections grow into the multi-TB range, you need shard-aware writes from day one, or your team prefers the aggregation pipeline mental model.

For most product startups, the answer is jsonb. The "we'll need MongoDB later for scale" decision is usually paid for in immediate complexity and almost never recouped.

A Realistic Schema Pattern

The pattern that ages well: keep the columns you query and join on as real columns, and put the long tail of optional fields into jsonb:

CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users(id),
  event_type  text   NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  properties  jsonb  NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX idx_events_user_time
  ON events (user_id, occurred_at DESC);
CREATE INDEX idx_events_props
  ON events USING GIN (properties jsonb_path_ops);

You get fast user-timeline queries from the B-tree, and any new event property is queryable through containment without a migration.

Working with PostgreSQL JSON in a SQL IDE

JSON columns are notoriously painful in most SQL IDEs — they render as a single line of unreadable text, you can't filter by path, and there's no way to peek at the document shape without writing a query.

Jam SQL Studio treats json and jsonb as first-class. Cells render as collapsible trees, the filter chip exposes JSONPath operators (has property, property =, property contains) that compile to @>, ->>, and the SQL/JSON path operators underneath, and a Peek popover scans the loaded rows to show you which paths exist and how often. The same UI works against nvarchar(max) on SQL Server, JSON on MySQL, CLOB on Oracle, and TEXT on SQLite — one mental model, five engines.

Last verified against the official PostgreSQL documentation on 2026-05-13.