PostgreSQL JSON and JSONB

PostgreSQL can store JSON documents in a column and query inside them, so you get a document store and a relational database in one. Use jsonb (binary, indexable, keys de-duplicated) rather than json (stored as plain text) unless you need the exact original text.

-> gets a field as jsonb; ->> gets it as text.
#> / #>> follow a path: data #>> '{address,city}'.
@> “contains”: attributes @> '{"wireless": true}' (can use a GIN index).
? “has key”, ?| any key, ?& all keys.
Build JSON: jsonb_build_object, jsonb_agg, to_jsonb. Modify it: jsonb_set, || (merge), - (remove a key). Take it apart: jsonb_each, jsonb_object_keys, jsonb_array_elements.

Good practice: keep core, frequently-filtered fields as real columns and use jsonb for the flexible, varying extras, like products.attributes here.

Syntax

attributes ->> 'brand'
attributes @> '{"wireless": true}'
jsonb_set(attributes, '{color}', '"black"')

Examples

Edit the code and run it. Output is printed the way psql prints it.

19.1Read fields from JSON

SELECT name,
       attributes,
       attributes ->> 'brand'            AS brand,
       attributes -> 'wireless'          AS wireless_json,
       (attributes ->> 'dpi')::int       AS dpi
FROM products
ORDER BY id
LIMIT 6;

->> returns text, so cast it (::int) for arithmetic or numeric comparisons. Missing keys give NULL, not an error.

19.2Filter with @> and ?

1
2

SELECT name FROM products WHERE attributes @> '{"wireless": true}';
SELECT name, attributes ->> 'resolution' AS res FROM products WHERE attributes ? 'resolution';

@> asks “does the left document contain this smaller document?”. ? asks whether a top-level key exists.

19.3Build JSON from rows (an API response)

SELECT jsonb_build_object(
         'customer', c.name,
         'orders', jsonb_agg(
             jsonb_build_object('id', o.id, 'date', o.order_date, 'status', o.status)
             ORDER BY o.order_date)
       ) AS doc
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1, 2)
GROUP BY c.id, c.name;

jsonb_agg collects one object per order into an array. The database can produce the exact JSON shape a web app needs.

19.4Change JSON and expand it into rows

BEGIN;
UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"black"') || '{"warranty_years": 2}'
WHERE id = 2
RETURNING attributes;

UPDATE products SET attributes = attributes - 'dpi' WHERE id = 2 RETURNING attributes;
ROLLBACK;

SELECT key, value FROM products, jsonb_each(attributes) WHERE id = 3;

jsonb_set sets a path, || merges another object, - removes a key. jsonb_each turns an object into key/value rows.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 19.1

List the name and brand (from attributes) of every wireless product.

Hint

WHERE attributes @> '{"wireless": true}'

Solution

SELECT name, attributes ->> 'brand' AS brand FROM products WHERE attributes @> '{"wireless": true}';

Exercise 19.2

Count products per brand: brand, products.

Hint

GROUP BY attributes ->> 'brand'

Solution

SELECT attributes ->> 'brand' AS brand, count(*) AS products FROM products GROUP BY 1;