# JSONB — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/a-jsonb

> Flexible attributes inside a relational table.

## 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.

```sql
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.

**Quiz:** Which operator returns a JSON field as text?

- [ ] ->
- [x] ->>
- [ ] @>
- [ ] ||

*Answer:* ->>. -> returns jsonb, ->> returns text.