Gestire in modo efficiente dati simili a array in MySQL utilizzando i tipi JSON

1. Introduzione

La necessità di gestire dati in stile array in MySQL

Nei database, i dati sono tipicamente archiviati con un design relazionale. Tuttavia, a seconda dei requisiti dell’applicazione, può essere utile memorizzare più valori in una singola colonna. In tali casi, una struttura dati come un “array” può essere utile.

Per esempio, consideriamo i seguenti casi:

• Memorizzare più tag selezionati da un utente.
• Salvare più URL di immagini per un prodotto.
• Consolidare la cronologia o i log in un unico campo.

Vantaggi di sfruttare il tipo JSON

MySQL non fornisce un tipo “array” diretto, ma utilizzando il tipo JSON, è possibile gestire dati in stile array. Il tipo JSON è altamente flessibile e offre i seguenti vantaggi:

• Supporta strutture dati annidate.
• Permette la manipolazione dei dati direttamente nelle query.
• Gestisce più formati di dati all’interno di un unico campo.

In questo articolo introdurremo come gestire efficientemente dati in stile array in MySQL utilizzando il tipo JSON.

2. Fondamenti della gestione degli array con il tipo JSON di MySQL

Che cos’è il tipo JSON?

JSON (JavaScript Object Notation) è un formato di scambio dati leggero e semplice. MySQL supporta un tipo JSON nativo a partire dalla versione 5.7, consentendo di memorizzare e manipolare dati formattati in JSON direttamente nel database.

Esempio: i seguenti dati possono essere memorizzati in un campo di tipo JSON:

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

Benefici e casi d’uso del tipo JSON

I principali vantaggi nell’utilizzare il tipo JSON sono:

1. Struttura dati flessibile : È possibile gestire dati di lunghezza variabile senza modificare lo schema relazionale.
2. Manipolazione dati efficiente : Utilizza le funzioni integrate di MySQL (es. JSON_EXTRACT, JSON_ARRAY) per manipolare facilmente i dati.
3. Progettazione senza schema : Non è necessario modificare lo schema frequentemente in base alle specifiche dell’applicazione.

Casi d’uso:

• Assegnare più categorie alle informazioni di un prodotto.
• Memorizzare impostazioni utente personalizzate.
• Utilizzare in applicazioni web che elaborano dati JSON annidati.

3. Operazioni di base sugli array JSON

Creazione di array JSON

In MySQL puoi utilizzare la funzione JSON_ARRAY per creare facilmente array formattati in JSON. Gli array sono utili quando si desidera memorizzare più valori all’interno di una singola colonna.

Esempio di utilizzo

Nella seguente query creiamo un array JSON chiamato tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Risultato:

["PHP", "MySQL", "JavaScript"]

Esempio applicato

Ecco un esempio di memorizzazione di un array JSON nel database tramite una dichiarazione INSERT.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Estrazione dei dati dagli array JSON

Per recuperare i dati memorizzati in un array JSON si utilizza la funzione JSON_EXTRACT. Consente di estrarre elementi specifici all’interno dell’array.

Esempio di utilizzo

Nell’esempio seguente recuperiamo il secondo elemento (indice 0-based) dell’array.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Risultato:

"MySQL"

Recupero di più elementi

Puoi anche ottenere più elementi contemporaneamente.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Aggiungere, aggiornare ed eliminare dati

Aggiunta di dati a un array

Utilizzando la funzione JSON_ARRAY_APPEND puoi aggiungere nuovi dati a un array esistente.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Risultato:

["PHP", "MySQL", "JavaScript"]

Aggiornamento dei dati all’interno di un array

Puoi aggiornare un elemento specifico all’interno dell’array utilizzando JSON_SET.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Risultato:

["PHP", "Python", "JavaScript"]

Rimozione di dati da un array

Usa JSON_REMOVE per eliminare un elemento specifico all’interno dell’array.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Risultato:

["PHP", "JavaScript"]

4. Ricerca e filtraggio degli array JSON

Ricerca di array contenenti dati specifici

Per determinare se un array JSON contiene un valore specifico utilizzi la funzione JSON_CONTAINS. Questa funzione restituisce se l’array JSON specificato contiene il valore indicato.

Esempio di utilizzo

Nell’esempio seguente verifichiamo se l’array JSON contiene “MySQL”.

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Risultato:

1  (if present)
0  (if not present)

Esempio applicato: ricerca condizionale

Se vuoi interrogare le righe nel database che contengono un array JSON con un valore specifico, puoi usare JSON_CONTAINS nella clausola WHERE.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

Questa query recupera le righe in cui la colonna tags include “MySQL”.

Ottenere la lunghezza di un array

Per ottenere il numero di elementi in un array JSON usa la funzione JSON_LENGTH. Questo è utile per l’analisi dei dati o per il filtraggio condizionale.

Esempio di utilizzo

Nell’esempio seguente otteniamo il numero di elementi dell’array.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Risultato:

3

Esempio pratico: estrarre righe che soddisfano una condizione

Per estrarre righe in cui il numero di elementi è uguale o maggiore di un certo valore, usa JSON_LENGTH nella clausola WHERE.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

Questa query seleziona righe in cui la colonna tags contiene due o più elementi.

Esempio pratico con query condizionali

Puoi combinare più condizioni per ricerche più avanzate. La seguente query trova righe in cui l’array tags contiene “JavaScript” e ha tre o più elementi.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Casi d’uso pratici: imparare attraverso scenari reali per gli array JSON

Come memorizzare le categorie di prodotto come array JSON

Ne i siti di e-commerce i prodotti possono appartenere a più categorie. In questi casi usare un array JSON per memorizzare le informazioni sulle categorie può essere efficiente.

Esempio: memorizzare i dati di categoria per i prodotti

Di seguito è riportato un esempio di creazione di una tabella con una colonna JSON chiamata categories e di memorizzazione di più categorie.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

Questa struttura dati consente una memorizzazione concisa anche quando un prodotto appartiene a più categorie.

Interrogare prodotti appartenenti a una categoria specifica

Sfruttando il tipo JSON, puoi facilmente cercare prodotti appartenenti a categorie specifiche.

Esempio di query

La seguente query trova tutti i prodotti nella categoria “Electronics”.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Risultato:

Laptop
Smartphone

Questa query ti consente di recuperare in modo flessibile elenchi di prodotti per categoria.

Esempio: filtrare per fascia di prezzo

Vediamo come memorizzare le informazioni sul prezzo in JSON e interrogare i prodotti in base a una fascia di prezzo.

Esempio di dati

Di seguito memorizziamo le informazioni sul prezzo dei prodotti per articolo utilizzando il tipo JSON.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Esempio di query

Per cercare prodotti con prezzo di 100.000 o più, si utilizza JSON_EXTRACT.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Risultato:

Laptop

Utilizzo di JSON_TABLE per l’espansione e esempi di query

Quando si vuole interrogare dati JSON in forma relazionale si può utilizzare JSON_TABLE. Questa funzione consente di espandere un array JSON come tabella virtuale.

Esempio di utilizzo

Di seguito un esempio in cui un array JSON viene espanso e ogni categoria viene visualizzata come riga separata.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Risultato:

category_name
--------------
Electronics
Computers
Mobile Devices

6. Precauzioni nell’utilizzo del tipo JSON

Punti di ottimizzazione delle prestazioni

Sebbene il tipo JSON sia altamente flessibile, senza un design appropriato può influire negativamente sulle prestazioni del database. Di seguito i punti chiave per l’ottimizzazione delle prestazioni.

1. Uso degli indici

In MySQL non è possibile impostare direttamente un indice su una colonna JSON, ma è possibile creare una colonna virtuale e indicizzare una chiave specifica.

Esempio: creazione di un indice tramite colonna virtuale

In questo esempio indicizziamo la chiave price all’interno dei dati JSON.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Utilizzando una colonna virtuale è possibile migliorare notevolmente le prestazioni della ricerca sui dati di tipo JSON.

2. Evitare strutture JSON troppo complesse

Strutture JSON profondamente annidate influenzano la leggibilità delle query e le prestazioni. Quando si progettano i dati, adottare una struttura JSON il più semplice possibile.

Buon esempio:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Struttura da evitare:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

Come utilizzare efficacemente gli indici

Quando si utilizzano colonne virtuali per la creazione di indici, è necessario considerare i seguenti punti:

1. La colonna virtuale deve essere STORED.
2. Utilizzare JSON_EXTRACT per estrarre chiavi specifiche nella colonna virtuale.

Ad esempio, per estrarre il valore della chiave categories e indicizzarlo:

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

L’importanza della validazione dei dati

Sebbene i dati di tipo JSON siano flessibili, comportano anche il rischio di memorizzare formati non corretti. Per mantenere l’integrità dei dati, utilizzare le seguenti soluzioni.

1. Uso delle restrizioni CHECK

A partire da MySQL 8.0 è possibile utilizzare le restrizioni CHECK per validare la struttura o il contenuto dei dati JSON.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Validazione a livello di applicazione

Durante l’inserimento dei dati è necessario validare il formato JSON a livello di applicazione. Nei linguaggi di programmazione come PHP o Python è possibile utilizzare librerie standard per validare JSON.

7. Domande frequenti sull’utilizzo di dati in stile array in MySQL

Q1: MySQL fornisce un tipo array?

A1: MySQL non dispone di un “tipo array” diretto. Tuttavia è possibile gestire dati in stile array utilizzando il tipo JSON. Con il tipo JSON è possibile memorizzare più valori in una singola colonna e manipolarli tramite query.

Esempio:

SELECT JSON_ARRAY('value1', 'value2', 'value3') AS example_array;

Risultato:

["value1", "value2", "value3"]

Q2: È possibile impostare un indice sui dati di tipo JSON?

A2: Non è possibile impostare direttamente un indice su un tipo JSON. È comunque possibile estrarre chiavi o valori specifici come colonna virtuale e quindi impostare un indice su quella colonna.

Esempio:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Questa modalità consente una ricerca efficiente dei valori all’interno dei dati JSON.

Q3: Esiste un limite di dimensione per i dati JSON?

A3: Il tipo JSON di MySQL può memorizzare fino a 4 GB di dati. Tuttavia, l’utilizzo di dati JSON molto grandi può ridurre le prestazioni, quindi una progettazione adeguata dei dati è essenziale.

Recommendations:

• Memorizza solo i dati minimi richiesti.
• Evita strutture JSON annidate in profondità.

Q4: Come si aggiorna un elemento specifico in un array JSON?

A4: È possibile aggiornare elementi specifici all’interno di un array JSON utilizzando la funzione JSON_SET.

Example:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Risultato:

["PHP", "Python", "JavaScript"]

Q5: Confronto tra il tipo JSON e il design tradizionale delle tabelle

A5: Il tipo JSON offre un’elevata flessibilità, ma differisce anche per natura dal design relazionale tradizionale.

8. Sommario

Benefici della gestione di dati a forma di array con il tipo JSON in MySQL

Questo articolo ha spiegato il tipo JSON per gestire dati a forma di array in MySQL. I punti principali trattati sono:

1. Perché usare il tipo JSON
   MySQL non dispone di un tipo array diretto, ma usando il tipo JSON è possibile memorizzare più valori in una colonna e eseguire operazioni sui dati in modo flessibile.

2. Operazioni di base JSON
   Si è imparato come creare array JSON, estrarre dati, aggiornare e cancellare valori.
   Si sono utilizzate funzioni come JSON_ARRAY, JSON_EXTRACT e JSON_SET per manipolare dati a forma di array in modo efficiente.

3. Ricerca e filtraggio
   Si è usato JSON_CONTAINS per cercare valori specifici all’interno degli array JSON.
   Si è usato JSON_LENGTH per ottenere la lunghezza dell’array e eseguire filtri condizionali.

4. Casi d’uso pratici
   * Abbiamo appreso scenari di applicazione concreti, come la gestione delle categorie di prodotto e il filtraggio per prezzo.

5. Precauzioni e ottimizzazione
   * È stato discusso come indicizzare i dati JSON tramite colonne virtuali e l’importanza di validare i dati JSON.

Prossimi passi per sfruttare il tipo JSON

Utilizzando il tipo JSON in MySQL è possibile garantire una gestione dei dati più flessibile rispetto al design relazionale tradizionale. Tuttavia, una progettazione adeguata e considerazioni sulle prestazioni sono essenziali.

Topics to learn next:

• Utilizzare indici compositi: Progettare indici che combinano colonne di tipo JSON con colonne regolari.
• Funzioni JSON avanzate: Utilizzare JSON_MERGE, JSON_OBJECT e altre funzioni per operazioni più complesse.
• Manipolazione dei dati a livello applicativo: Manipolare efficacemente i dati JSON di MySQL utilizzando linguaggi come PHP o Python.

Sommario finale

Attraverso questo articolo hai imparato come gestire dati a forma di array in modo efficiente usando il tipo JSON in MySQL. Applicando questa conoscenza potrai progettare sistemi di database più flessibili e scalabili.