Working with JSONB in PostgreSQL

JSONB stores JSON in a binary format that PostgreSQL can query and index efficiently. It's ideal for data whose shape varies: webhook payloads, settings, product attributes.

JSON vs JSONB

json stores the text as-is; jsonb parses it into a binary form, drops duplicate keys and whitespace, and supports indexing. Use jsonb almost always.

Operators you'll use

CREATE TABLE products (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  TEXT NOT NULL,
  attrs JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO products (name, attrs) VALUES
  ('Laptop', '{"brand": "Acme", "ram_gb": 16, "ports": ["usb-c", "hdmi"], "warranty": {"years": 2}}');

SELECT attrs->'warranty'         FROM products;  -- JSON value: {"years": 2}
SELECT attrs->>'brand'           FROM products;  -- text: Acme
SELECT attrs#>>'{warranty,years}' FROM products; -- text at a path: 2
SELECT (attrs->>'ram_gb')::int   FROM products;  -- cast to compare numerically

Filtering

SELECT name FROM products WHERE attrs @> '{"brand": "Acme"}';     -- containment
SELECT name FROM products WHERE attrs ? 'warranty';                -- key exists
SELECT name FROM products WHERE attrs->'ports' ? 'usb-c';          -- array contains string
SELECT name FROM products WHERE (attrs->>'ram_gb')::int >= 16;

Updating

UPDATE products SET attrs = jsonb_set(attrs, '{warranty,years}', '3') WHERE id = 1;
UPDATE products SET attrs = attrs || '{"color": "silver"}' WHERE id = 1;   -- merge
UPDATE products SET attrs = attrs - 'color' WHERE id = 1;                  -- remove a key

Indexing

CREATE INDEX products_attrs_idx ON products USING gin (attrs jsonb_path_ops);   -- for @>
CREATE INDEX products_brand_idx ON products ((attrs->>'brand'));               -- one hot field

When to use real columns instead

• Fields that every row has and that you filter, sort or join on.
• Data that needs constraints (NOT NULL, foreign keys, CHECK).
• A hybrid is common: core fields as columns, the variable remainder in a JSONB column.

For reporting, JSON_TABLE (PostgreSQL 17+) turns JSON arrays into rows you can query like a table.