MENU
  1. ホーム
  2. SQL
  3. Come gestire e convertire efficacemente i dati di array in SQL

Come gestire e convertire efficacemente i dati di array in SQL

SQL

Nei database SQL, la manipolazione dei dati relazionali è predominante, ma recentemente c’è un crescente bisogno di gestire anche i dati di array. Questo è particolarmente vero quando si tratta di dati in formato JSON o XML, dove è richiesta una gestione efficiente degli array. In questo articolo, esploreremo metodi specifici e esempi di query per gestire e convertire efficacemente i dati di array in SQL.

Indice

Concetti di base sui dati di array e come gestirli in SQL

I dati di array si riferiscono a una struttura di dati in cui elementi dello stesso tipo sono ordinati sequenzialmente. Sebbene SQL abbia funzionalità limitate per la gestione diretta degli array, i database SQL moderni permettono la gestione dei dati di array utilizzando formati come JSON o XML.

Esempio di dati di array

Ad esempio, ecco un array in formato JSON.

[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"},
  {"id": 3, "name": "Charlie"}
]

Gestione di base dei dati di array in SQL

In SQL, per memorizzare i dati di array in una tabella, si utilizzano i tipi JSON o XML. Database come PostgreSQL e MySQL supportano il tipo JSON, che può essere utilizzato per memorizzare e manipolare i dati di array. Di seguito è riportato un esempio di come memorizzare dati di array in una tabella utilizzando il tipo JSON in PostgreSQL.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO users (data) VALUES
('[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"},
  {"id": 3, "name": "Charlie"}
]');

Come convertire un array in un formato tabellare

Convertire i dati di array in un formato tabellare rende più facile la loro manipolazione utilizzando le normali query SQL. Di seguito viene illustrato un metodo per convertire i dati di array JSON in formato tabellare utilizzando PostgreSQL.

Espandere un array in righe singole

È possibile espandere un array JSON in righe singole utilizzando la funzione jsonb_array_elements di PostgreSQL.

SELECT jsonb_array_elements('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]') AS element;

Questa query restituisce il seguente risultato.

 element
-----------------------------
 {"id": 1, "name": "Alice"}
 {"id": 2, "name": "Bob"}
 {"id": 3, "name": "Charlie"}

Convertire i dati di array espansi in una tabella

Dopo aver espanso i dati di array in righe singole, è possibile convertire ciascun elemento in colonne di una tabella.

SELECT
  element->>'id' AS id,
  element->>'name' AS name
FROM
  jsonb_array_elements('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]') AS element;

Questa query restituisce il seguente risultato.

 id |  name
----+---------
 1  | Alice
 2  | Bob
 3  | Charlie

Inserire i dati in una tabella

È anche possibile inserire i dati espansi in una nuova tabella.

CREATE TABLE users (
  id INT,
  name TEXT
);

INSERT INTO users (id, name)
SELECT
  (element->>'id')::INT,
  element->>'name'
FROM
  jsonb_array_elements('[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]') AS element;

Questo consente di convertire i dati di array in una tabella relazionale standard, permettendo l’esecuzione di operazioni SQL standard.

Gestione degli array utilizzando i dati JSON

L’uso dei dati JSON permette una gestione più flessibile ed efficiente degli array in SQL. Di seguito viene illustrato come gestire gli array utilizzando i dati JSON in PostgreSQL.

Inserimento e query di dati JSON

Prima di tutto, vediamo come inserire i dati JSON in una tabella e come eseguire query su di essi.

Creazione della tabella e inserimento dei dati JSON

Ecco i comandi SQL per creare una tabella che contiene dati JSON e per inserire i dati.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO users (data) VALUES
('{"users": [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]}');

Query dei dati JSON

Mostriamo come eseguire una query sui dati JSON inseriti per ottenere le informazioni desiderate. Ad esempio, per ottenere il nome di un utente, si utilizza la seguente query.

SELECT
  data->'users'->0->>'name' AS first_user_name
FROM
  users;

Questa query restituisce il nome del primo utente nell’array.

Accesso ai singoli elementi dell’array

Per accedere e manipolare i singoli elementi di un array, si utilizza la funzione jsonb_array_elements.

SELECT
  jsonb_array_elements(data->'users') AS user
FROM
  users;

Questa query restituisce ciascun utente come riga separata.

Accesso alle proprietà di ciascun elemento

Mostriamo come accedere alle proprietà di ciascun elemento espanso.

SELECT
  user->>'id' AS id,
  user->>'name' AS name
FROM
  users,
  jsonb_array_elements(data->'users') AS user;

Questa query restituisce l’ID e il nome di ciascun utente.

Utilizzo delle funzioni JSON

PostgreSQL offre una varietà di funzioni per manipolare i dati JSON. Ad esempio, per ottenere la lunghezza di un array si può usare la funzione jsonb_array_length.

SELECT
  jsonb_array_length(data->'users') AS number_of_users
FROM
  users;

Questa query restituisce la lunghezza dell’array di utenti.

L’uso dei dati JSON consente una manipolazione più flessibile degli array, rendendo la gestione dei dati più efficiente.

Unione e filtraggio dei dati di array

Unendo e filtrando i dati di array, è più facile estrarre le informazioni necessarie. Di seguito viene illustrato come unire e filtrare i dati di array in PostgreSQL.

Unione dei dati di array

Unendo più array JSON, è possibile combinare le informazioni correlate tra diversi array. La seguente query mostra come unire più array JSON.

Esempio: Unione dei dati di utenti e ordini

Prima di tutto, creiamo tabelle che contengano dati di utenti e ordini, inserendo i relativi dati JSON.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  data JSONB
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO users (data) VALUES
('{"users": [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]}');

INSERT INTO orders (data) VALUES
('{"orders": [{"user_id": 1, "product": "Laptop"}, {"user_id": 2, "product": "Tablet"}, {"user_id": 3, "product": "Smartphone"}]}');

Successivamente, eseguiamo una query per unire i dati di utenti e ordini.

SELECT
  u.user->>'name' AS user_name,
  o.order->>'product' AS product
FROM
  (SELECT jsonb_array_elements(data->'users') AS user FROM users) AS u,
  (SELECT jsonb_array_elements(data->'orders') AS order FROM orders) AS o
WHERE
  u.user->>'id' = o.order->>'user_id';

Questa query restituisce l’unione tra ciascun utente e il suo ordine.

Filtraggio dei dati di array

Vediamo come filtrare i dati di array per estrarre solo gli elementi che soddisfano determinati criteri.

Esempio: Filtraggio di un utente specifico

La seguente query filtra e restituisce solo l’utente con il nome “Alice”.

SELECT
  user->>'id' AS id,
  user->>'name' AS name
FROM
  users,
  jsonb_array_elements(data->'users') AS user
WHERE
  user->>'name' = 'Alice';

Questa query restituisce solo l’utente con il nome “Alice”.

Esempio: Filtraggio di utenti che hanno ordinato un prodotto specifico

La seguente query filtra e restituisce gli utenti che hanno ordinato un “Laptop”.

SELECT
  u.user->>'name' AS user_name,
  o.order->>'product' AS product
FROM
  (SELECT jsonb_array_elements(data->'users') AS user FROM users) AS u,
  (SELECT jsonb_array_elements(data->'orders') AS order FROM orders) AS o
WHERE
  u.user->>'id' = o.order->>'user_id' AND
  o.order->>'product' = 'Laptop';

Questa query restituisce gli utenti che hanno ordinato un “Laptop” e le relative informazioni sull’ordine.

L’uso dell’unione e del filtraggio dei dati di array consente operazioni dati complesse e rende più efficiente l’estrazione delle informazioni necessarie.

Gestione avanzata degli array con funzioni finestra

L’uso delle funzioni finestra consente di eseguire analisi avanzate e aggregazioni sui dati di array. Di seguito viene illustrato come gestire i dati di array utilizzando le funzioni finestra in PostgreSQL.

Nozioni di base sulle funzioni finestra

Le funzioni finestra eseguono calcoli su un set di righe correlate, senza dover raggruppare le righe stesse, rendendole utili per analisi dettagliate dei dati.

Esempio: Classificazione dei dati di array

Inseriamo in una tabella i dati di array contenenti i punteggi degli utenti e li classifichiamo in base a questi punteggi.

CREATE TABLE user_scores (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO user_scores (data) VALUES
('{"users": [{"id": 1, "name": "Alice", "score": 85}, {"id": 2, "name": "Bob", "score": 90}, {"id": 3, "name": "Charlie", "score": 75}]}');

Successivamente, eseguiamo una query per classificare gli utenti in base al loro punteggio.

SELECT
  user->>'name' AS name,
  user->>'score' AS score,
  RANK() OVER (ORDER BY (user->>'score')::INT DESC) AS rank
FROM
  user_scores,
  jsonb_array_elements(data->'users') AS user;

Questa query restituisce una classifica degli utenti in base al punteggio, dal più alto al più basso.

  name   | score | rank
---------+-------+------
  Bob    | 90    | 1
  Alice  | 85    | 2
  Charlie| 75    | 3

Esempio: Calcolo della media mobile

È anche possibile calcolare la media mobile dei dati di array utilizzando le funzioni finestra. Nel seguente esempio, calcoliamo la media mobile delle vendite mensili di un utente.

CREATE TABLE monthly_sales (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO monthly_sales (data) VALUES
('{"sales": [{"month": "January", "amount": 100}, {"month": "February", "amount": 200}, {"month": "March", "amount": 150}, {"month": "April", "amount": 300}]}');

Ora eseguiamo una query per calcolare la media mobile.

SELECT
  sale->>'month' AS month,
  (sale->>'amount')::INT AS amount,
  AVG((sale->>'amount')::INT) OVER (ORDER BY sale->>'month' ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM
  monthly_sales,
  jsonb_array_elements(data->'sales') AS sale;

Questa query restituisce le vendite di ciascun mese e la media mobile corrispondente.

  month    | amount | moving_avg
-----------+--------+------------
  January  | 100    | 150
  February | 200    | 150
  March    | 150    | 216.67
  April    | 300    | 225

L’uso delle funzioni finestra consente un’analisi avanzata e un’aggregazione dei dati di array, fornendo approfondimenti più dettagliati sui dati.

Ottimizzazione delle prestazioni per i dati di array

Per gestire efficacemente i dati di array, è fondamentale ottimizzare le prestazioni. Di seguito vengono presentate le migliori pratiche e tecniche per ottimizzare la gestione dei dati di array in SQL.

Utilizzo degli indici

Gli indici possono velocizzare la ricerca e la manipolazione dei dati di array. In particolare, quando si utilizza il tipo di dati JSONB, si consiglia di utilizzare l’indice GIN.

CREATE INDEX idx_users_data ON users USING GIN (data);

Questo indice consente di cercare in modo efficiente campi specifici all’interno dei dati JSONB.

Rimozione dei dati inutili

Se nei dati di array sono presenti campi non necessari, questi possono rallentare le operazioni. Estrarre e manipolare solo i campi necessari può migliorare le prestazioni.

SELECT
  user->>'id' AS id,
  user->>'name' AS name
FROM
  users,
  jsonb_array_elements(data->'users') AS user
WHERE
  user->>'id' IS NOT NULL;

Questa query estrae solo i campi necessari, rimuovendo i dati inutili.

Utilizzo dell’inserimento in blocco

Quando si inseriscono grandi quantità di dati di array, l’inserimento in blocco può risultare efficiente. Questo minimizza l’overhead dell’inserimento.

INSERT INTO users (data) VALUES
('{"users": [{"id": 4, "name": "David"}, {"id": 5, "name": "Eva"}]}'),
('{"users": [{"id": 6, "name": "Frank"}, {"id": 7, "name": "Grace"}]}');

Inserendo più record contemporaneamente, si ottimizza il processo di inserimento.

Esecuzione regolare di VACUUM e ANALYZE

In PostgreSQL, l’esecuzione regolare di VACUUM e ANALYZE impedisce la crescita eccessiva delle tabelle e mantiene aggiornate le statistiche.

VACUUM ANALYZE users;

Questo consente al pianificatore di query di selezionare il piano di query ottimale in base alle statistiche più recenti.

Scelta del tipo di dati appropriato

Anche la scelta del tipo di dati influisce sulle prestazioni. Ad esempio, quando si gestiscono dati JSON, il tipo JSONB offre prestazioni superiori rispetto al tipo JSON, pertanto si consiglia di utilizzare JSONB.

Utilizzo del parallelismo

Quando si gestiscono grandi set di dati, l’utilizzo del parallelismo può migliorare le prestazioni. PostgreSQL consente l’esecuzione parallela delle query, sfruttando più core della CPU.

SET max_parallel_workers_per_gather = 4;

Questa impostazione consente l’esecuzione parallela delle query, migliorando la velocità di elaborazione.

Utilizzando queste tecniche di ottimizzazione, è possibile gestire in modo efficiente i dati di array e migliorare notevolmente le prestazioni delle query SQL.

Conclusione

Per gestire e convertire efficacemente i dati di array in SQL, è necessario prestare attenzione a diversi punti chiave. Utilizzando tipi di dati come JSONB, è possibile gestire meglio i dati di array e convertirli in formato tabellare utilizzando funzioni come jsonb_array_elements, permettendo così operazioni SQL standard. Inoltre, sfruttando funzioni finestra, indici, rimozione dei dati inutili, inserimenti in blocco, esecuzione regolare di VACUUM e ANALYZE, scelta del tipo di dati appropriato e parallelismo, è possibile ottimizzare le prestazioni al massimo. Combinando queste tecniche, è possibile gestire in modo efficiente i dati di array in SQL.

SQL
Indice