JSON and JSONB

json vs jsonb

Two types, and the choice is almost always jsonb.

Use json only when you must hand back the exact bytes you were given, for example a signed webhook payload. Everything else is jsonb. Numbers in jsonb become numeric, so 1.0 reads back as 1.0 and 1e2 as 100.

Extracting values

Four operators do all the reading. The >> forms return text, the > forms return jsonb. Chaining -> and finishing with ->> is the normal idiom.

SELECT metadata ->> 'region'                    AS region,
       (metadata -> 'items' -> 0 ->> 'sku')     AS first_sku,
       metadata #>> '{items,0,qty}'             AS first_qty,
       (metadata ->> 'qty')::int                AS qty_int
FROM orders;

Negative array indexes count from the end, so -> -1 is the last element. A missing key yields NULL, not an error, and NULL ->> 'x' is NULL, which is how a typo in a key name silently becomes a whole column of nulls.

Containment and existence

These are the operators a GIN index can answer, so they are the ones worth writing queries around.

SELECT * FROM orders WHERE metadata @> '{"region":"eu","paid":true}';
SELECT * FROM orders WHERE metadata @> '{"items":[{"sku":"a"}]}';   -- array containment
SELECT * FROM orders WHERE metadata ? 'coupon';

@> matches nested structure, not just top-level keys, and it treats arrays as sets, so '[1,2,3]' @> '[3,1]' is true. ? only sees the top level.

Modifying jsonb

Every one of these returns a new value, so an update is a plain SET col = f(col).

UPDATE orders SET metadata = jsonb_set(metadata, '{region}', '"us"') WHERE id = 1;
UPDATE orders SET metadata = metadata || '{"paid":true,"paid_at":"2026-03-01"}';
UPDATE orders SET metadata = metadata - 'coupon' WHERE metadata ? 'coupon';
UPDATE orders SET metadata = jsonb_set(metadata, '{items,0,qty}', to_jsonb(9));
UPDATE orders SET metadata = jsonb_insert(metadata, '{items,-1}', '{"sku":"c"}', true);

|| is a shallow merge. Merging {"a":{"x":1}} with {"a":{"y":2}} gives {"a":{"y":2}}, not a deep union. For a deep merge, set the nested path directly. The literal on the right must be valid JSON, so wrap plain values in to_jsonb(...) rather than string-concatenating quotes.

jsonpath and SQL/JSON

jsonpath (12+) is the query language for anything past a single key lookup. $ is the document, @ is the current item inside a filter, and ?( ) filters.

SELECT jsonb_path_query(metadata, '$.items[*].sku')             FROM orders;
SELECT jsonb_path_query_array(metadata, '$.items[*].qty')       FROM orders;
SELECT jsonb_path_query_first(metadata, '$.items[*] ? (@.qty > 1)') FROM orders;
SELECT * FROM orders WHERE metadata @? '$.items[*] ? (@.qty > 5)';
SELECT * FROM orders WHERE metadata @@ '$.region == "eu"';

-- Bind parameters instead of interpolating
SELECT jsonb_path_query(metadata, '$.items[*] ? (@.qty > $min)',
                        vars => '{"min":1}') FROM orders;

The SQL/JSON functions are 17+ and read better in application queries:

SELECT json_value(metadata, '$.region' RETURNING text)  FROM orders;  -- one scalar
SELECT json_query(metadata, '$.items' RETURNING jsonb)  FROM orders;  -- any value
SELECT * FROM orders WHERE json_exists(metadata, '$.coupon');

-- json_table flattens an array into real rows (17+)
SELECT o.id, jt.sku, jt.qty
FROM orders o,
     JSON_TABLE(o.metadata, '$.items[*]'
       COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty')) AS jt;

json_value errors if the result is not a single scalar. Use json_query when it might be an object or array.

Expanding and building

-- Rows out of an embedded array, keeping the parent id
SELECT o.id, e ->> 'sku' AS sku, (e ->> 'qty')::int AS qty
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.metadata -> 'items') AS e;

SELECT o.id, i.sku, i.qty
FROM orders o
CROSS JOIN LATERAL jsonb_to_recordset(o.metadata -> 'items') AS i(sku text, qty int);

-- Rows into JSON
SELECT jsonb_agg(jsonb_build_object('id', id, 'total', total) ORDER BY id) AS payload
FROM orders;

Use CROSS JOIN LATERAL rather than putting the function in the select list, or a row with an empty array disappears from the result.

Indexing jsonb

CREATE INDEX orders_meta_gin  ON orders USING gin (metadata);
CREATE INDEX orders_meta_path ON orders USING gin (metadata jsonb_path_ops);
CREATE INDEX orders_region    ON orders ((metadata ->> 'region'));
CREATE INDEX orders_paid_meta ON orders USING gin (metadata)
  WHERE metadata ->> 'status' = 'open';

A GIN index cannot serve ->> equality and a btree expression index cannot serve @>, so match the index to the operator you actually write. jsonb_path_ops indexes paths plus values rather than every key, which is why it cannot answer key-existence ?.

When jsonb is the wrong choice

jsonb is for genuinely variable data, third-party payloads, sparse attributes, and event bodies. It is not a way to avoid writing DDL.

- If every row has the key, it is a column. Columns get types, constraints, defaults, cheap statistics, and a btree index without an expression. - The planner has almost no statistics inside a jsonb value, so selectivity estimates for @> are guesses and joins built on extracted keys plan badly. - A jsonb column over ~2 kB is TOASTed and compressed, so every read of one key detoasts the whole document. - There are no foreign keys, no NOT NULL, and no type safety inside a document. {"qty":"3"} and {"qty":3} both store fine and compare differently. - Updating one key rewrites the entire value, which means a full row rewrite plus every index on it.

A good default: real columns for the fields you query and constrain, one jsonb column named metadata for the rest.