PostgreSQL JSONB: When and How to Store JSON in a Table

The short answer: json or jsonb

PostgreSQL has two JSON types:

Rule of thumb: choose jsonb almost every time. Use json only when you must keep the document byte for byte, for example to verify the signature of an incoming request.

Core operators

Take a products table:

CREATE TABLE products (
  id     bigserial PRIMARY KEY,
  name   text NOT NULL,
  price  numeric(14, 2) NOT NULL,
  attrs  jsonb NOT NULL DEFAULT '{}'
);

INSERT INTO products (name, price, attrs) VALUES
  ('Laptop', 1000.00,
   '{"brand": "Lenovo", "ram_gb": 16, "colors": ["black", "grey"], "dims": {"weight_kg": 1.4}}');

• -> returns the value as jsonb: attrs -> 'dims'.
• ->> returns the value as text: attrs ->> 'brand'.
• #> and #>> do the same by path: attrs #>> '{dims,weight_kg}'.
• @> means "contains": attrs @> '{"brand": "Lenovo"}'.
• ? checks whether a top-level key exists: attrs ? 'ram_gb'.

SELECT name, attrs ->> 'brand' AS brand
FROM products
WHERE attrs @> '{"colors": ["black"]}'
  AND (attrs ->> 'ram_gb')::int >= 16;

Note that ->> always returns text, so numeric comparisons need an explicit cast.

Indexes

A GIN index on the whole column speeds up @>, ?, ?| and ?&:

CREATE INDEX products_attrs_gin ON products USING GIN (attrs);

If you only search with @>, use the jsonb_path_ops operator class — the index is more compact but does not support ?:

CREATE INDEX products_attrs_path ON products USING GIN (attrs jsonb_path_ops);

A B-tree expression index suits a single key you often filter or sort by:

CREATE INDEX products_ram ON products (((attrs ->> 'ram_gb')::int));

The query must use exactly the same expression as the index, or the planner will not pick it up.

Updating nested fields

-- replace a value at a path
UPDATE products SET attrs = jsonb_set(attrs, '{dims,weight_kg}', '1.3') WHERE id = 1;

-- add or overwrite top-level keys
UPDATE products SET attrs = attrs || '{"warranty_months": 12}' WHERE id = 1;

-- remove a key and a nested path
UPDATE products SET attrs = attrs - 'colors' WHERE id = 1;
UPDATE products SET attrs = attrs #- '{dims,weight_kg}' WHERE id = 1;

Details that matter:

• jsonb_set creates only the last key of the path. If an intermediate object (dims) is missing, the document stays unchanged.
• || merges only the top level: a nested object is replaced as a whole, not merged.
• Recent PostgreSQL versions also support subscript updates: SET attrs['brand'] = '"HP"'.
• Every change rewrites the whole document. Large JSON values that are frequently edited one field at a time add needless load.

When a JSON column makes sense

• Product attributes: a phone has storage and screen size, shoes have size and material. Shared fields (name, price, stock, category) are columns; type-specific ones go into jsonb.
• Raw payment webhooks from Payme, Click and other providers. Keep the full payload for resolving disputes, but pull the transaction id, amount and status into columns:

CREATE TABLE payment_events (
  id           bigserial PRIMARY KEY,
  provider     text NOT NULL,
  external_id  text NOT NULL,
  status       text NOT NULL,
  amount       numeric(14, 2) NOT NULL,
  payload      jsonb NOT NULL,
  received_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (provider, external_id, status)
);

The unique key protects you from processing the same notification twice if the provider resends it.

• Settings and metadata that are read as a whole and rarely filtered.
• External API responses whose format you do not control.

When it becomes an anti-pattern

Warning signs:

• the field exists in every row and appears in almost every WHERE;
• you JOIN on the field, or it stores ids of other records — you lose foreign keys;
• you need constraints: NOT NULL, uniqueness, types, CHECK;
• the field drives money and reports: sums, grouping;
• different parts of the code write the same thing under different keys.

One more reason: the planner does not keep detailed statistics on keys inside jsonb, so estimates for complex queries can be off. Once a field becomes important, move it into a column with a migration.