jsonb è il tipo di colonna JSON binario di PostgreSQL. Leggi i campi con -> (restituisce jsonb) e ->> (restituisce text), trovi le righe con l'operatore di contenimento @> e modifichi i campi con jsonb_set o ||. Un indice GIN rende veloci queste query.
Corrispondono gli ordini 1 e 3. data->'customer' restituisce l'oggetto annidato come jsonb, ->>'name' estrae il nome come testo, e ->> più un cast trasforma il totale in un numero.
Estrarre valori da JSONB
| Operatore | Restituisce | Esempio |
|---|---|---|
-> chiave o indice | jsonb | data->'customer', data->'tags'->0 |
->> chiave o indice | text | data->>'status' |
#> percorso | jsonb | data#>'{customer,city}' |
#>> percorso | text | data#>>'{customer,city}' |
[...] subscript (PostgreSQL 14+) | jsonb | data['customer']['city'] |
Gli indici degli array partono da 0, e un indice negativo conta dalla fine (-> -1 è l'ultimo elemento). Una chiave mancante dà NULL SQL, non un errore, quindi l'ordine 3 qui sopra ha una città NULL.
Usa ->> per confrontare con del testo o per fare un cast: data->>'status' = 'paid'. Usa -> per continuare a navigare. Un errore comune è data->'status' = 'paid'. PostgreSQL legge 'paid' come JSON, una parola senza virgolette non è JSON valido, e la query fallisce con "invalid input syntax for type json".
Trovare righe in base al contenuto JSON
@> controlla se il valore di sinistra contiene quello di destra. Funziona a qualsiasi profondità e dentro gli array, e un indice GIN lo supporta:
I prodotti 1 e 2 hanno la taglia M e un prezzo; solo il 2 costa più di 30. Gli operatori di ricerca:
@>contiene,<@è contenuto in.?la chiave (o la stringa dell'array) esiste,?|esiste almeno una delle chiavi,?&esistono tutte.@?e@@eseguono una query JSON path (PostgreSQL 12+), per i confronti dentro il documento.
Aggiornare campi dentro JSONB
PostgreSQL riscrive l'intero valore a ogni aggiornamento, ma queste funzioni ti permettono di descrivere solo la modifica:
jsonb_set(target, path, new_value)sostituisce un valore in un percorso.-rimuove una chiave, e#-rimuove un percorso annidato come'{notify,sms}'.||aggiunge o sovrascrive le chiavi di primo livello.
Da PostgreSQL 14 puoi anche assegnare tramite subscript: SET prefs['notify']['email'] = 'false'.
Due trappole:
jsonb_setrestituisceNULLquando il nuovo valore èNULLSQL, e l'UPDATEcancella allora l'intera colonna. PassaCOALESCE(to_jsonb(x), 'null'), oppure usajsonb_set_lax(PostgreSQL 13+), che di default salva unnullJSON.jsonb_setaggiunge un'ultima chiave mancante, ma non i genitori mancanti: impostare'{a,b}'su{}lo lascia invariato. L'assegnazione tramite subscript invece li crea.
Trasformare array JSON in righe
jsonb_array_elements restituisce una riga per ogni elemento dell'array, e jsonb_to_recordset trasforma un array di oggetti in colonne tipizzate. Da PostgreSQL 17, lo standard JSON_TABLE fa entrambe le cose in un solo punto:
A-1 ha venduto 3 e B-2 ha venduto 1. Nella direzione opposta, jsonb_build_object('id', id, 'name', name) costruisce un oggetto dalle colonne e jsonb_agg raccoglie le righe in un array (vedi string_agg e array_agg).
Indicizzare JSONB con GIN
Un indice GIN sull'intera colonna serve @>, ?, ?|, ?&, @? e @@:
CREATE INDEX orders_data_idx ON orders USING gin (data);
-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);
-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));
Scrivi la query nella forma che l'indice supporta. WHERE data @> '{"status": "paid"}' può usare l'indice GIN. WHERE data->>'status' = 'paid' no; ha bisogno dell'indice sull'espressione. Un controllo su un intervallo come (data->>'total')::numeric > 50 richiede un indice proprio su quell'espressione.
JSON vs JSONB
json | jsonb | |
|---|---|---|
| Salvato come | Il testo in ingresso | Una forma binaria già analizzata |
| Spazi, ordine delle chiavi, chiavi duplicate | Mantenuti | Rimossi; vince l'ultimo duplicato |
| Scrittura | Un po' più veloce (solo validato) | Analizzato all'ingresso |
| Lettura e query | Rianalizzato a ogni accesso | Veloce |
@>, ? e indici GIN | No | Sì |
json restituisce il testo invariato; jsonb tiene solo "a": 3 e riordina le chiavi. Usa jsonb a meno che tu non debba restituire il testo originale byte per byte, come il payload salvato di un webhook. Tieni in colonne normali i campi che ogni riga ha, o che hanno bisogno di una chiave esterna. Il riferimento ufficiale delle funzioni JSON elenca ogni operatore e funzione.
Domande frequenti
Che differenza c'è tra -> e ->> in PostgreSQL?
-> restituisce il valore come jsonb, così puoi continuare a navigare (data->'customer'->'city'). ->> lo restituisce come text, che è ciò che confronti con una stringa o converti in numero: (data->>'total')::numeric.
Come controllo se un array JSONB contiene un valore?
Usa il contenimento: data->'tags' @> '["gift"]', oppure data @> '{"tags": ["gift"]}' per controllare dalla radice del documento. Per un array di stringhe funziona anche data->'tags' ? 'gift'. Un indice GIN su data velocizza la seconda forma.
JSONB mantiene l'ordine delle chiavi?
No. jsonb salva le chiavi in un suo ordine, rimuove gli spazi e tiene solo l'ultima delle chiavi duplicate. Se ti serve riavere esattamente il testo originale, usa il tipo json.
Come controllo se una chiave esiste in JSONB?
data ? 'key' è vero quando l'oggetto di primo livello ha quella chiave, anche se il suo valore è null JSON. ?| accetta un array ed è vero se esiste almeno una delle chiavi, ?& se esistono tutte.
Posso aggiungere una chiave esterna o un vincolo NOT NULL a un campo dentro JSONB?
Non direttamente. Un vincolo CHECK può controllare un campo (CHECK (data ? 'email')), ma le chiavi esterne hanno bisogno di una colonna vera. I campi che ogni riga ha e a cui fanno riferimento altre tabelle vanno in colonne normali.