JSONB in PostgreSQL — what it is, how it works, and when to use it

← Back to the section

Sometimes the shape of the data is not known in advance. Different products in a catalog have different attributes, different events in an audit log have different fields, and a separate column for each case is inconvenient. For exactly these cases PostgreSQL can store JSON right inside a column.

json and jsonb — what's the difference

PostgreSQL offers two types: json and jsonb. The difference is fundamental.

json stores the text as-is — literally a string with curly braces, and PostgreSQL re-parses it on every query, which is slow. On the upside, json keeps key order and duplicates (rarely needed, occasionally important).

jsonb stores data in a parsed binary form — a ready-made structure, fast to read and to search. Key order is not preserved, duplicates are dropped (the last one wins).

The normalization jsonb performs is easy to reproduce by hand: a duplicate key is overwritten by the last value, and the keys are laid out by name length, then alphabetically.

live example

import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class JsonbNormalize {
    public static void main(String[] args) {
        String raw = "{\"b\": 1, \"a\": 2, \"long_key\": 3, \"a\": 9}";
        List<String[]> pairs = List.of(
                new String[]{"b", "1"}, new String[]{"a", "2"},
                new String[]{"long_key", "3"}, new String[]{"a", "9"});

        Map<String, String> jsonb = new TreeMap<>(
                Comparator.comparingInt(String::length).thenComparing(Comparator.naturalOrder()));
        for (String[] pair : pairs) {
            jsonb.put(pair[0], pair[1]);
        }

        System.out.println("json : " + raw);
        System.out.println("jsonb: " + jsonb);
    }
}

It prints json : {"b": 1, "a": 2, "long_key": 3, "a": 9} and jsonb: {a=9, b=1, long_key=3}. PostgreSQL answers with {"a": 9, "b": 1, "long_key": 3} — its own order, one surviving duplicate, no extra whitespace.

In practice you almost always want jsonb. The exception is preserving the input string bit-for-bit — for a cryptographic signature, say.

The main rule: column or JSON key

Before you tuck a field away in JSONB, it is worth asking one question: will this field be used for filtering, sorting, or JOINs?

If yes — it is a column, not a JSON key. A column is indexed, typed, and fast. A JSON key without a special index means a full table scan.

A common mistake: dump everything into JSONB, then wonder why the queries are slow:

-- bad: email and status are fields that get searched constantly
CREATE TABLE customer (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data jsonb NOT NULL
);

SELECT * FROM customer WHERE data->>'email' = 'ivan@example.com'; -- full scan
SELECT * FROM customer WHERE data->>'status' = 'ACTIVE';          -- full scan

The right approach keeps main fields as columns and puts only rarely-filtered data in JSONB:

-- good: main fields as columns, flexible attributes in jsonb
CREATE TABLE customer (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      citext      NOT NULL UNIQUE,
    status     varchar(20) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    metadata   jsonb       NOT NULL DEFAULT '{}'::jsonb
);

When JSONB really helps

Event log

The ORDER_PAID event carries one set of fields, USER_REGISTERED — another. Nobody creates hundreds of columns for every possible event type.

CREATE TABLE event_log (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    aggregate_id uuid        NOT NULL,
    event_type   varchar(50) NOT NULL,
    occurred_at  timestamptz NOT NULL,
    payload      jsonb       NOT NULL
);

The event_type column is for filtering and indexing. The polymorphic content goes into payload.

Attributes of products in different categories

Sneakers have a size and a shoe-last width, laptops have memory capacity and a screen diagonal. None of that needs a column of its own — 200 nullable fields are awkward both for queries and for reading the code.

CREATE TABLE product (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku           varchar(50) NOT NULL UNIQUE,
    name          text NOT NULL,
    price         numeric(15, 2) NOT NULL,
    attributes    jsonb NOT NULL DEFAULT '{}'::jsonb
);

-- {"size": 42, "material": "leather"}
-- {"diagonal": 15.6, "ram_gb": 16}

Integration configurations

Every notification channel has its own settings. One JSONB column is much simpler than a separate table per type.

CREATE TABLE integration_channel (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kind   varchar(20) NOT NULL,   -- 'SMTP', 'TELEGRAM', 'WEBHOOK'
    config jsonb NOT NULL
);

Snapshot of an external API response

The structure is dictated by an external service and can change. JSONB captures "what the API returned" without a schema change every time.

Core operators

Queries lean on a handful of operators:

live example

SELECT
    payload -> 'items'                  AS items,          -- returns jsonb
    payload ->> 'orderId'               AS order_id,       -- returns text
    payload #> '{items, 0}'             AS first_item,     -- path → jsonb
    payload #>> '{items, 0, productId}' AS first_product   -- path → text
FROM outbox
WHERE event_type = 'OrderCreated';

The -> operator gives a nested object (type jsonb), ->> gives a string (type text). For a nested path, use #> and #>>.

Check whether a document contains the required keys or a subset:

-- the document contains the subset
SELECT * FROM event_log WHERE payload @> '{"event_type": "ORDER_PAID"}'::jsonb;

-- the column contains a key
SELECT * FROM product WHERE attributes ? 'material';

-- contains at least one of the keys
SELECT * FROM product WHERE attributes ?| array['material', 'size'];

-- contains all of the listed keys
SELECT * FROM product WHERE attributes ?& array['material', 'size'];

The @> operator asks about containment, not equality: a document matches when it holds every key-value pair of the sample. The same rule in one line:

live example

import java.util.Map;

public class JsonbContains {
    public static void main(String[] args) {
        Map<String, Object> payload = Map.of(
                "event_type", "ORDER_PAID", "amount", 1990, "currency", "RUB");

        Map<String, Object> byType = Map.of("event_type", "ORDER_PAID");
        Map<String, Object> byTypeAndAmount = Map.of("event_type", "ORDER_PAID", "amount", 100);

        System.out.println("@> {event_type}          -> " + contains(payload, byType));
        System.out.println("@> {event_type, amount}  -> " + contains(payload, byTypeAndAmount));
    }

    static boolean contains(Map<String, Object> document, Map<String, Object> query) {
        return document.entrySet().containsAll(query.entrySet());
    }
}

It prints true and false. The second sample failed not on a missing key but on the value: in the document amount is 1990.

Indexes — how to speed up JSONB search

Without an index, a JSONB search is a full scan. Two options.

A GIN index is a good fit when you filter by different keys or nested objects via the @> operator. The jsonb_path_ops variant is smaller and faster than the standard GIN, but it cannot do everything: it supports containment @> and jsonpath queries (@?, @@), while a key-existence check (?, ?|) is not indexed by it:

CREATE INDEX event_log_payload_gin
    ON event_log USING gin (payload jsonb_path_ops);

-- now this runs fast
SELECT * FROM event_log WHERE payload @> '{"event_type": "ORDER_PAID"}';

A functional index is cheaper than GIN and better when you always search by one specific key:

CREATE INDEX event_log_event_type
    ON event_log ((payload ->> 'event_type'));

-- speeds up point lookups
SELECT * FROM event_log WHERE payload ->> 'event_type' = 'ORDER_PAID';

If you always filter by the same key, prefer the functional index; GIN is for varied conditions or a nested structure.

What you should not store in JSONB

JSONB is for small structured documents. A few things should not go in there:

• Binary data (images, files) — use bytea or external storage for those.
• Long text (articles, descriptions) — better use text; PostgreSQL will place large values efficiently on its own.
• base64 data — this is binary content encoded into text with extra overhead.

If a JSONB field grows to tens of kilobytes per row, that is a signal to reconsider the design.

JSONB has one more price — updates. A single key cannot be changed in place: PostgreSQL rewrites the whole document as a new row version, and a document over a couple of kilobytes goes to TOAST and is read back in pieces. A field that changes often — a balance, a counter, a status — bloats the table; its place is a regular column.

Working with JSONB in application code

The driver serializes the object to JSON on the way in and deserializes it on read. Typed objects are better: the structure is visible, and it is clear what is required.

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.jooq.Converter;
import org.jooq.JSONB;

public class JsonbConverter implements Converter<JSONB, JsonNode> {

    private final ObjectMapper mapper = new ObjectMapper();

    @Override
    public JsonNode from(JSONB db) {
        try { return db == null ? null : mapper.readTree(db.data()); }
        catch (Exception e) { throw new IllegalStateException(e); }
    }

    @Override
    public JSONB to(JsonNode node) {
        try { return node == null ? null : JSONB.valueOf(mapper.writeValueAsString(node)); }
        catch (Exception e) { throw new IllegalStateException(e); }
    }
}

public record EventPayload(UUID customerId, String eventType, Instant occurredAt, Map<String, Object> details) {}

import (
    "context"
    "encoding/json"
    "github.com/jackc/pgx/v5/pgxpool"
)

type EventPayload struct {
    CustomerID string         `json:"customerId"`
    EventType  string         `json:"eventType"`
    OccurredAt string         `json:"occurredAt"`
    Details    map[string]any `json:"details"`
}

func insertEvent(ctx context.Context, pool *pgxpool.Pool, p EventPayload) error {
    raw, err := json.Marshal(p)
    if err != nil {
        return err
    }
    _, err = pool.Exec(ctx,
        `INSERT INTO event_log (aggregate_id, event_type, occurred_at, payload)
         VALUES ($1, $2, now(), $3)`,
        p.CustomerID, p.EventType, raw,
    )
    return err
}

func loadPayload(ctx context.Context, pool *pgxpool.Pool, id int64) (EventPayload, error) {
    var raw []byte
    err := pool.QueryRow(ctx, `SELECT payload FROM event_log WHERE id = $1`, id).Scan(&raw)
    if err != nil {
        return EventPayload{}, err
    }
    var p EventPayload
    return p, json.Unmarshal(raw, &p)
}

import { Pool } from 'pg';

interface EventPayload {
  customerId: string;
  eventType: string;
  occurredAt: string;
  details: Record<string, unknown>;
}

const pool = new Pool();

async function insertEvent(p: EventPayload): Promise<void> {
  await pool.query(
    `INSERT INTO event_log (aggregate_id, event_type, occurred_at, payload)
     VALUES ($1, $2, now(), $3)`,
    [p.customerId, p.eventType, JSON.stringify(p)],
  );
}

async function loadPayload(id: number): Promise<EventPayload> {
  const { rows } = await pool.query<{ payload: EventPayload }>(
    `SELECT payload FROM event_log WHERE id = $1`,
    [id],
  );
  return rows[0].payload; // pg deserializes jsonb into an object automatically
}

from dataclasses import dataclass, asdict
from typing import Any
import psycopg
from psycopg.rows import dict_row


@dataclass
class EventPayload:
    customer_id: str
    event_type: str
    occurred_at: str
    details: dict[str, Any]


async def insert_event(conn: psycopg.AsyncConnection, p: EventPayload) -> None:
    await conn.execute(
        """
        INSERT INTO event_log (aggregate_id, event_type, occurred_at, payload)
        VALUES (%s, %s, now(), %s)
        """,
        (p.customer_id, p.event_type, asdict(p)),  # psycopg serializes the dict automatically
    )


async def load_payload(conn: psycopg.AsyncConnection, event_id: int) -> EventPayload:
    async with conn.cursor(row_factory=dict_row) as cur:
        await cur.execute("SELECT payload FROM event_log WHERE id = %s", (event_id,))
        row = await cur.fetchone()
        return EventPayload(**row["payload"])

Common mistakes

Everything in a single data jsonb column. It looks flexible, but it costs typing, indexes, and readability. A year later nobody knows what keys are in there and what is required.

A field that gets searched sits in JSONB without an index. Email, status, identifier — those are columns.

JSONB instead of migrations. Adding new "fields" through JSON keys to avoid writing ALTER TABLE is tempting but dangerous. The schema diverges between services, the data stops being validated, and nobody knows the schema.

In short

• You almost always want jsonb, not json — binary storage, fast search, GIN support.
• If a field is used for filtering, sorting, or JOINs — it is a column, not a JSON key.
• JSONB is a good fit for: event logs, polymorphic attributes, integration configurations, external API snapshots.
• -> returns jsonb, ->> returns text; @> checks for subset containment.
• GIN with jsonb_path_ops speeds up @>; a functional index is cheaper for a single specific key.
• Binary data, long text, and base64 do not belong in JSONB, and the document itself should not grow beyond a few kilobytes: updating one key rewrites all of it.

What to read next

• Arrays and range types in PostgreSQL — when an array beats JSONB for lists.
• Indexes in PostgreSQL — more on GIN and other index types.
• String types in PostgreSQL — when to use text instead of JSONB.
• EXPLAIN ANALYZE — how to make sure the index on JSONB is actually used.