JSONB

Operators and updates

jsonb stores JSON in a binary form that can be indexed and queried. Key operators: -> returns JSON, ->> returns text, @> tests containment, ? tests for a key, || merges objects. Use JSONB for attributes that vary between rows (product specifications, settings, raw webhook payloads), but keep core, frequently queried and related data in normal columns with constraints.

Querying and updating JSONB, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. Containment finds the blue notebook and extracts color and pages; the ? operator finds the product with an ink key; || adds an on_sale flag to the desk lamp's attributes, returned with RETURNING.

SELECT name, attrs->>'color' AS color, (attrs->>'pages')::int AS pages
FROM products
WHERE attrs @> '{"color": "blue"}';

SELECT name FROM products WHERE attrs ? 'ink';

UPDATE products SET attrs = attrs || '{"on_sale": true}' WHERE name = 'Desk lamp'
RETURNING name, attrs;

Output:

   name   | color | pages 
----------+-------+-------
 Notebook | blue  |   200
(1 row)

     name     
--------------
 Fountain pen
(1 row)

   name    |                      attrs                      
-----------+-------------------------------------------------
 Desk lamp | {"color": "white", "watts": 9, "on_sale": true}
(1 row)

Promote hot keys to columns

If you filter or join on a JSON key constantly, move it into a real column (or a generated column) with proper types and indexes.

Quick check: Which operator returns a JSON field as text?

• ->
• ->>
• @>
• ||