Menu

PostgreSQL JSONB: operatori, query e indici

Salvare e interrogare JSON in PostgreSQL con jsonb: gli operatori -> e ->>, il contenimento con @>, jsonb_set e gli altri aggiornamenti, gli array trasformati in righe, gli indici GIN e json vs jsonb.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

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

OperatoreRestituisceEsempio
-> chiave o indicejsonbdata->'customer', data->'tags'->0
->> chiave o indicetextdata->>'status'
#> percorsojsonbdata#>'{customer,city}'
#>> percorsotextdata#>>'{customer,city}'
[...] subscript (PostgreSQL 14+)jsonbdata['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_set restituisce NULL quando il nuovo valore è NULL SQL, e l'UPDATE cancella allora l'intera colonna. Passa COALESCE(to_jsonb(x), 'null'), oppure usa jsonb_set_lax (PostgreSQL 13+), che di default salva un null JSON.
  • jsonb_set aggiunge 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

jsonjsonb
Salvato comeIl testo in ingressoUna forma binaria già analizzata
Spazi, ordine delle chiavi, chiavi duplicateMantenutiRimossi; vince l'ultimo duplicato
ScritturaUn po' più veloce (solo validato)Analizzato all'ingresso
Lettura e queryRianalizzato a ogni accessoVeloce
@>, ? e indici GINNoSì

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.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia