PostgreSQL JSON & JSONB - PostgreSQL Tutorial

Postgres can store JSON documents alongside relational columns — and index them.

JSON vs JSONB

	`JSON`	`JSONB`
Stored as	exact text	parsed binary
Preserves key order/whitespace	yes	no
Indexable	no	yes
Query speed	slower	faster

Use JSONB unless you specifically need to preserve the original text byte-for-byte.

SQL

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

INSERT INTO products (name, attributes) VALUES
('Notebook', '{"pages": 200, "ruled": true, "tags": ["stationery", "school"]}');

Reading values

SQL

SELECT attributes -> 'pages'     FROM products;  -- JSONB: 200
SELECT attributes ->> 'pages'    FROM products;  -- TEXT:  "200"
SELECT attributes #> '{tags,0}'  FROM products;  -- nested path → JSONB
SELECT attributes #>> '{tags,0}' FROM products;  -- nested path → TEXT

Remember the rule: one arrow returns JSON, two arrows return text.

SQL

SELECT * FROM products WHERE (attributes ->> 'pages')::INT > 100;

Containment and existence

SQL

SELECT * FROM products WHERE attributes @> '{"ruled": true}';
SELECT * FROM products WHERE attributes ? 'pages';          -- key exists
SELECT * FROM products WHERE attributes ?| ARRAY['a','b'];  -- any key
SELECT * FROM products WHERE attributes ?& ARRAY['a','b'];  -- all keys

Indexing

SQL

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

This makes @>, ?, ?|, ?& fast. For a specific field you query constantly, a plain B-tree expression index is smaller:

SQL

CREATE INDEX idx_products_pages ON products (((attributes ->> 'pages')::INT));

Modifying JSONB

SQL

UPDATE products
SET attributes = attributes || '{"in_stock": true}'   -- merge
WHERE id = 1;

UPDATE products
SET attributes = jsonb_set(attributes, '{pages}', '250')
WHERE id = 1;

UPDATE products
SET attributes = attributes - 'ruled'                  -- remove key
WHERE id = 1;

Expanding JSON into rows

SQL

SELECT p.name, tag
FROM products p,
     jsonb_array_elements_text(p.attributes -> 'tags') AS tag;

SELECT key, value FROM jsonb_each_text('{"a":1,"b":2}');

Building JSON from rows

Very handy for API endpoints — return a whole nested structure in one query:

SQL

SELECT jsonb_build_object(
    'id', b.id,
    'title', b.title,
    'author', jsonb_build_object('name', a.name)
) AS row
FROM books b JOIN authors a ON a.id = b.author_id;

SELECT to_jsonb(b) FROM books b;                 -- whole row as JSON
SELECT jsonb_agg(to_jsonb(b)) FROM books b;      -- array of rows

When to use JSONB

Good for genuinely variable attributes — product specs that differ per category, third-party API payloads, audit snapshots.

Bad as a substitute for columns. If every row has the field and you query it constantly, make it a real column: you get type checking, constraints, and better performance.