Alcuni articoli fa avevo trattato la diatriba, ancora attuale, sull’uso di database Relazionali o NoSQL. Ciascuna tecnologia, profondamente diversa l’una dall’altra, possiede ovviamente vantaggi e svantaggi anche se entrambe possono essere applicate alla stessa soluzione: tuttavia, se proviamo ad utilizzare un database NoSQL quando uno relazionale sarebbe di gran lunga superiore, il risultato è che ci complicheremo la vita inutilmente, solo magari per andare incontro a una “moda”. Ad esempio, quando memorizziamo il nome di un utente in vari luoghi per un facile accesso, ma quando l’utente aggiorna il proprio nome siamo costretti a trovare tutti quei luoghi per assicurarci che le sue informazioni siano aggiornate. In questo caso, i database relazionali non hanno concorrenti. Durante la fase di analisi della nostra applicazione, dovremmo ben valutare se i nostri dati saranno “incapsulati” all’interno di una scatola (e allora un NoSql andrebbe bene) e se queste scatole interagiranno tra di loro (e allora forse converrebbe valutare l’uso di un Relazionale).
Nulla vieterebbe ad usare entrambi i database per sfruttare le potenzialità di entrambi: di sicuro aumenterebbe la complessità di sviluppo. In una sola parola, chi ce lo fa fare?
Ma ecco che ci viene in aiuto Postgres (anche se devo dire che MySql si è adeguato da un po’). Dalla versione 9.2, Postgres ha introdotto un nuovo tipo di dati, per l’appunto JSON. La JavaScript Object Notation, è un potente formato di interscambio per la rappresentazione strutturata e annidata di dati in formato testuale, di facile comprensione e ampiamente utilizzata nei linguaggi Javascript di ultima generazione (e dal 2017 è uno standard IETF 8259).
Il supporto JSON su Postgres esiste da un bel po’, ma ad essere onesti non era eccezionale a causa della mancanza di metodi di indicizzazione e di estrazione. Con il rilascio della versione 9.2, Postgres ha aggiunto il supporto JSON nativo. Ciò significa che possiamo finalmente usare Postgres come database “NoSQL”. Nella versione 9.3, Postgres ha migliorato il supporto aggiungendo ulteriori metodi di costruzione ed estrazione. 9.4 ha aggiunto la possibilità di memorizzare JSON come “Binary JSON” (JSONB), che elimina gli spazi bianchi insignificanti (non è un grosso problema), aggiunge un piccolo sovraccarico durante l’inserimento dei dati, ma offre un enorme vantaggio durante l’interrogazione: gli indexes.
Con il rilascio della versione 9.4 e il completo supporto a JSON la domanda “Uso un documento o un database relazionale?” non è più necessario, perchè possiamo utilizzarli entrambi. Sia chiaro però che Postgres è e rimane un database relazionale, quindi non potrà mai gestire JSON come MongoDB, che dopotutto è creato appositamente come database di documenti JSON. Ma la verità è che Postgres ora gestisce abbastanza bene JSON.
Perché usare JSON in un database relazionale?
Se un utente fa acquisti e lascia recensioni, è abbastanza comune modellare i dati in ingresso attraverso un database relazionale. La ragione è appunto che i dati provenienti da un sito web tendono ad essere relazionali. Tuttavia, ci sono casi d’uso in cui ha senso incorporare un documento JSON. Ad esempio, è perfetto quando:
- si tende ad evitare dei join complicati. Ad esempio qualcosa di simile a Trello, dove i dati si possono conservare in una singola scheda. Avere i dati denormalizzati rende possibile recuperare una scheda e i suoi dati con una singola query
- mentere i dati che provengono da un servizio esterno nella stessa struttura e formato (come JSON) con cui sono arrivati. Ad esempio le risposte di servizi API. Ad esempio, l’oggetto di risposta all’addebito di Stripe è particolarmente complesso e annidato. Invece di tentare di normalizzare questi dati su cinque o più tabelle, è possibile archiviarli così come sono (e continuare a eseguire query su di essi)
- Evitare di trasformare i dati prima di restituirli in JSON tramite un API. Costruire infatti strutture JSON in tempo reale su ogni richiesta sarebbe incredibilmente oneroso per il sistema
Json e Jsonb
PostgreSQL ha più tipi di dati relativi a JSON
- json
- jsonb
Qual è la differenza tra json e jsonb? Il tipo di dati JSON archivia una copia esatta del JSON (in un tipo di dati di testo) e deve essere analizzato nuovamente per essere utilizzato. È quindi più veloce da memorizzare, ma più lento da usare. Conserva anche l’ordine delle chiavi degli oggetti e delle chiavi degli oggetti duplicati, mentre jsonb no.
Il tipo di dati jsonb memorizza il JSON in un formato binario “scomposto” che è inizialmente più lento da memorizzare (a causa dell’overhead di conversione), ma più veloce da elaborare successivamente (poiché non è richiesta la ri-analisi) e potrebbe utilizzare un po’ più di disco spazio. Il vantaggio principale è che jsonb supporta l’indicizzazione, che consente query più efficienti.
Ecco una tabella per riassumere:
| Caratteristica | json | jsonb |
| Velocità di archiviazione | Più veloce | Più lentamente |
| Ordine chiave e duplicati | SÌ | NO |
| Velocità di recupero | Più lentamente | Più veloce |
| Spazio sul disco | Meno | Di più |
| Indicizzazione | No (ma puoi utilizzare gli indici delle funzioni per estrarre i percorsi json) | SÌ |
Anche se jsonb originariamente significava “JSON binario”, in realtà significa “JSON migliore” e dovrebbe essere il tipo di dati JSON da preferire rispetto al JSON testuale. Le applicazioni possono essere immense.
Operazioni su tabelle contenenti un documento json
Per creare la tabella, si utilizza la stessa notazione delle tabelle classiche, specificando il tipo jsonb per i campi interessati:
CREATE TABLE tabella1 ( id SERIAL NOT NULL PRIMARY KEY, jdata jsonb NOT NULL );
Per inserire un record all’interno della tabella:
INSERT INTO tabella1 VALUES (1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}');
In questo caso, il campo jdata conterrà una struttura dati che potrà essere successivamente interrogata. Ad esempio, possiamo scrivere una query di selezione, in modo tale da restituire solo un campo specifico della struttura json, in questo modo:
SELECT jdata->>'name' AS name FROM tabella1
In questo modo tireremo fuori solo l’informazione che ci interessa e non tutta la stringa. Lo stesso meccanismo può anche essere utilizzato in una condizione WHERE.
Per strutture JSON più complesse, si può utilizzare questa notazione, per prelevare i dati dai livelli più “profondi” e annidati della struttura. Ad esempio:
SELECT myjson::json->'name' as CityName, myjson::json->'coord'->'lon' as Longitude,myjson::json->'coord'->'lat' as Latitude FROM json_table;
L’aspetto principale del supporto JSON oltre al nuovo tipo di dati sono pure le funzioni e gli operatori supplementari. Gli operatori costituiscono un pilastro per il lavoro che viene svolto con gli oggetti JSON. Ecco un breve elenco delle novità più importanti:
- nuovi operatori: ->, ->>, #> e #>>
- nuove funzioni: json_array_length, json_extract_path, json_array_elements e molte altre
A volte nelle query si vede ::json e a volte no, qual è la differenza? Gli operatori ->, ->>, #> e #>> richiedono che il tipo di dati di input json. Se PostgreSQL può convertire implicitamente il parametro in json, puoi omettere ::json. Se non può garantire il casting, bisogna aggiungere il prefisso ::json al nome del campo e poi gli operatori possono essere utilizzati.
Un quadro completo e analitico delle funzioni e degli operatori disponibili per il tipo JSON sono consultabili da qui: https://www.postgresql.org/docs/current/functions-json.html
Utilizzo di array in PostgreSQL
Quando si pensa alle tabelle SQL, probabilmente viene in mente il concetto di record, cioè una singola riga per descrivere una precisa entità senza strutture nidificate. Ma cosa succederebbe se volessimo memorizzare un elenco di numeri, prodotti, ecc. all’interno di una riga?
Ovviamente potremmo “esplodere” l’elenco in molte righe, ma questo può portare a una giungla e problemi di normalizzazione di cui potrebbe essere difficile tenere traccia. PostgreSQL offre il tipo di dati Array che può essere utilizzato per archiviare e manipolare elenchi.
Con Array PostgreSQL consente di definire le colonne di una tabella come array multidimensionali di lunghezza variabile. È possibile creare array di qualsiasi tipo, enum, composito, intervallo o dominio incorporato o definito dall’utente. Gli array possono assumere molte forme e ci sono diversi modi per dichiararli.
- È possibile utilizzare la parola chiave ARRAY dopo la dichiarazione del tipo di dati per una colonna per indicare che si desidera creare un array del tipo di dati dichiarato in precedenza
- È possibile anche seguire la dichiarazione del tipo di dati con parentesi quadre (ad esempio text[]) ma trovo che questo sia meno esplicito e non sia conforme allo standard SQL.
Nell’esempio che segue, creiamo una tabella che descrive un carrello di un ecommerce utilizzando un’array tra i campi della tabella. Sempre di seguito, si descrive anche una query di inserimento e una query di selezione:
CREATE TEMP TABLE carrello (
id serial PRIMARY KEY,
prodotti text ARRAY
);
INSERT INTO
carrello(prodotti)
VALUES
(ARRAY['product_a', 'product_b']),
(ARRAY['product_c', 'product_d']),
(ARRAY['product_a', 'product_b', 'product_c']),
(ARRAY['product_a', 'product_b', 'product_d']),
(ARRAY['product_b', 'product_d']);
SELECT
*
FROM
carrello;
Un modo alternativo per aggiungere un Array a una tabella consiste nell’usare la sintassi {} invece della parola chiave ARRAY. Entrambi i modi funzionano, dobbiamo solo prestare molta attenzione a rispettare la sintassi delle virgolette e delle parentesi graffe.
Annullamento dell’annidamento dell’array
E se avessimo bisogno di “esplodere” o disfare l’array per interagire con delle righe specifiche, ad esempio quando vogliamo accedere a un singolo elemento per ulteriori operazioni? Nessun problema, gli array in PostgreSQL possono essere facilmente “esplosi” con la parola chiave UNNEST:
SELECT
id,
UNNEST(prodotti) AS prd
FROM
carrello
WHERE
id IN (3, 4);
In questo modo, UNNEST crea una riga per elemento nel rispettivo array nella riga originale:
Accesso agli elementi dell’array
Anzichè annullare l’annidamento di un array ogni volta possiamo in alternativa accedere al singolo elemento tramite l’indicizzazione con parentesi e sezioni come mostrato di seguito. Ciò consente una facile selezione, ad esempio, del primo elemento in ogni elenco (con la sintassi prodotti[1]) o di un intervallo specifico di elementi in un elenco (con la sintassi prodotti[inizio:fine]). Nota: l’indicizzazione negli array PostgreSQL inizia da 1, non da 0.
SELECT
id,
prodotti[1] AS primo_prodotto -- l'indice da 1
FROM
carrello;
Filtrare per un elemento Array
E se volessimo selezionare tutti i carrelli della spesa che hanno un prodotto specifico nell’array? O qualsiasi altra operazione di filtro basata su un elemento dell’array? Possiamo utilizzare il valore dell’elemento nella clausola WHERE insieme alla parola chiave ANY. Nell’esempio di seguito filtriamo le righe che contengono “product_c” nella colonna Array dei prodotti. Questo metodo di filtraggio è particolarmente utile per i singoli valori di filtro.
SELECT
id,
prodotti
FROM
carrello
WHERE
'product_c' = ANY (prodotti);
Aggiornamento dell’array
Gli array e i valori degli array possono essere aggiornati in modo simile ad altri tipi di dati, con una clausola UPDATE … SET … come mostrato di seguito. È possibile aggiornare un singolo elemento dell’array tramite l’indicizzazione o l’intero array.
// modifica di tutto l'array UPDATE carrello SET podotti = ARRAY['product_a','product_b','product_e'] WHERE id = 1; // modifica del singolo elemento dell'array UPDATE carrello SET podotti[1] = 'product_f' WHERE id = 2;
Rimozione di un elemento dell’array
Se vuoi sbarazzarti di uno specifico elemento dell’Array puoi usare UPDATE…SET… insieme alla funzione ARRAY_REMOVE. Ciò consente di rimuovere un elemento dagli Array per tutte le righe o solo un elemento specifico se utilizzato con una clausola WHERE.
UPDATE
carrello
SET
prodotti = array_remove(prodotti, 'product_e')
WHERE id = 1;
Queste sono solo alcune delle funzionalità che Postgres ha previsto per la gestione degli array. Per maggiori informazioni consultare la documentazione ufficiale che può essere raggiunta da questo link: https://www.postgresql.org/docs/current/arrays.html.
