Come inserire ed estrarre dati JSON in MySQL e in MariaDB, con esempi

di Giulio di Bari :: 16 ottobre 2020

Condividi su

Capita molto spesso di salvare in un campo, di una tabella MySQL o MariaDB, un dato in formato JSON, tuttavia questi due database gestiscono questo formato in modo differente.

MySQL implementa nativamente JSON a partire dalla versione 5.7.8

MariaDB ha implementato JSON a partire dalla versione 10.2.7 ma utilizzandolo come alias della tipologia LONGTEXT, per cui quando crei una tabella con un campo json, come vederemo tra poco, in campo sarà salvato come LONGTEXT.

Spero tu già sappia tutto sui dati JSON! In caso contrario, facciamo una veloce introduzione.

Un oggetto JSON è, fondamentalmente, una stringa racchiusa tra parentesi graffe, che contiene coppie chiave/valori, separate da virgola, come in questo esempio

{"name": "Ronaldo", "age": 35}

I valori possono essere stringhe, numeri, arrays, valori booleani ,...

Un array JSON contiene una lista di valori, sepati da virgola, e racchiusi tra parentesi quadre, come in questo esempio

["mario", 10, null, true]

Un array JSON può contenere oggetti json come in questo esempio

[
    {
      "name": "Buffon",
      "age": 41
    },
    {
      "name": "Ronaldo",
      "age": 35
    }
]

Ecco un esempio più articolato di oggetto json che contiene una chiave "giocatori", che è un array json e che contiene a sua volta due oggetti json (due giocatori)

{
  "squadra": "Juve",
  "citta": "Torino",
  "giocatori": [
    {
      "name": "Buffon",
      "age": 41
    },
    {
      "name": "Ronaldo",
      "age": 35
    }
  ]
}

Finita queste breve introduzione sui dati JSON occupiamoci di come utilizzare questa tipologia di dato in MySQL e in MariaDB.

Creazione di una tabella con un campo json

Vogliamo creare una tabella, dal nome "visite", dove vengono salvati gli accessi al nostro sito. I campi di questa tabella sono i seguenti

• id: è un intero auto incrementale
• data_accesso: è la data di accesso dell'utente
• pagina: è la pagina visitata dall'utente
• specifiche: una lista di spefiche relative all'utente, come il browser utilizzato, il sistema operativo e la risoluzione dello schermo. Questo campo sarà in formato JSON e salverà alcune coppie "chiave / valore" dell'utente come il browser utilizzato, il sistema operativo, la risoluzione dello schermo.

CREATE TABLE `visite` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `data_accesso` datetime NOT NULL,
  `pagina` varchar(50) NOT NULL,
  `specifiche` json NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Un campo JSON ha alcune limitazioni: non può avere un valore predefinito, non essere usato come chiave primaria, o essere referenziato come chiave esterna o avere un indice, quindi evita di utilizzarlo su colonne che vengono ricercate regolarmente. Tuttavia, a fine articolo, vedremo un escamotage sul modo in cui indicizzare un campo json.

Tieni inoltre presente che ci sono alcune differenze tra MySQL e MariaDB nell'utlizzo di JSON. La prima differenza è che, se utilizzi MariaDB, il campo JSON in realtà verrà salvato come come campo di testo di tipo LONGTEXT per cui la tipologia JSON è semplicemente un alias di LONGTEXT.

Aggiungere dati json nella tabella

Salviamo tre records in questa tabella

INSERT INTO `visite` (`data_accesso`, `pagina`, `specifiche`) VALUES

('2020-10-11 09:52:40', 'servizi', '{ "browser": "Firefox", "os": "Windows", "resolution": { "x": 2560, "y": 1600 } }'),

('2020-10-12 07:23:12', 'chi siamo', '{ "browser": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'),

( '2020-10-12 09:53:21', 'home page', '{ "browser": "Firefox", "os": "Windows", "resolution": { "x": 1600, "y": 900 } }');

Come possiamo vedere, nel campo json salviamo coppie di "chiave/valore", rispettando il formato dell'oggetto json.

Tuttavia, digitando "a mano" questo formato json, potremmo commettere errori di scrittura. Ci viene in aiuto una apposita funzione chiamata JSON_OBJECT

Per ottenere questo oggetto JSON

{ "browser": "Firefox", "os": "Windows"}

possiamo utilizzare la JSON_OBJECT in questo modo

JSON_OBJECT('browser','Firefox','os','Windows')

Mentre, per ottenere l'oggetto JSON che prevedere anche il dato "resolution" che a sua volta è in formato json (quindi abbiamo un json interno ad un json)

{ "browser": "Firefox", "os": "Windows", "resolution": { "x": 2560, "y": 1600 } }

possiamo utilizzare la JSON_OBJECT in questo modo

JSON_OBJECT('browser','Firefox','os','Windows','resolution', JSON_OBJECT('x','2560','y','1600') )

Utilizziamo quanto abbiamo appreso per effettuare l'INSERT dei nostri tre records

INSERT INTO `visite` (`data_accesso`, `pagina`, `specifiche`) VALUES

('2020-10-11 09:52:40', 'servizi', JSON_OBJECT('browser','Firefox','os','Windows','resolution', JSON_OBJECT('x','2560','y','1600') ) ),

('2020-10-12 07:23:12', 'chi siamo', JSON_OBJECT('browser','Safari','os','Mac','resolution', JSON_OBJECT('x','1920','y','1080') ) ),

( '2020-10-12 09:53:21', 'home page', JSON_OBJECT('browser','Firefox','os','Windows','resolution', JSON_OBJECT('x','1600','y','900') ) );

Volendo, possiamo fare in modo che, in fase di inserimento dati, venga effettuare un controllo di validità dei dati inseriti nel campo JSON.

Per fare questo, nella CREATE TABLE, utilizziamo "CHECK JSON_VALID" in questo modo

CREATE TABLE `visite` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `data_accesso` datetime NOT NULL,
  `pagina` varchar(50) NOT NULL,
  `specifiche` json NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CHECK (JSON_VALID(specifiche))
) ENGINE=InnoDB;

Selezionare chiavi in un campo json

Ad esempio, se vogliamo effetturare una SELECT in cui estrarre dal campo "specifiche" solo i valori dei "browser", utilizziamo questa query in cui utilizziamo la funzione JSON_EXTRACT

SELECT id,JSON_EXTRACT(specifiche, '$.browser') as browser FROM visite

La funziona JSON_EXTRACT richiede 2 argomenti: il campo json, e il dato da estrarre all'interno del campo json, scritto con il dollaro "$", seguito dal punto, e dalla chiave da estrarre.

Il risultato sarà

+----+-----------+
| id | browser   |
+----+-----------+
|  1 | "Firefox" |
|  2 | "Safari"  |
|  3 | "Firefox" |
+----+-----------+

Attenzione, il risultato comprende, per ogni valore, anche le virgolette!! Per rimuoverle utilizziamo JSON_UNQUOTE combinato con JSON_EXTRACT

SELECT JSON_UNQUOTE(JSON_EXTRACT(specifiche, '$.browser')) as browser FROM visite

+----+---------+
| id | browser |
+----+---------+
|  1 | Firefox |
|  2 | Safari  |
|  3 | Firefox |
+----+---------+

Solo in MySQL, in alternativa a JSON_EXTRACT, possiamo utilizzare l'operatore freccia: "->", che è un suo alias, in questo modo

SELECT specifiche->"$.browser" FROM visite

Il risultato sarà il medesimo. E per rimuovere le virgolette utilizziamo la doppia freccia "->>"

SELECT specifiche->>"$.browser" FROM visite

Vediamo adesso effettuare un "count", e nel nostro esempio come contare il numero di browser dei nostri utenti

SELECT JSON_EXTRACT(specifiche, '$.browser') as browser, count(*) FROM visite GROUP BY JSON_EXTRACT(specifiche, '$.browser') 

Il risultato sarà

+-----------+----------+
|  browser  | count(*) |
+-----------+----------+
| "Safari"  |     1    |
| "Firefox" |     2    |
+-----------+----------+

E solo il mysql possiamo utilizzare l'operatore freccia, così

SELECT specifiche->"$.browser" as browser, count(*) FROM visite GROUP BY specifiche->"$.browser"

Vediamo come estrarre da tutti i record la chiave risoluzione orizzontale "x", che sappiamo essere una chiave interna ad un'altra chiave "resolution": ci basta concatenare le due chiavi così

SELECT id,JSON_EXTRACT(specifiche, '$.resolution.x') as risolx FROM visite

ed in MySQL il codice alternativo è

SELECT id,specifiche->>"$.resolution.x" as risolx FROM visite

Il risultato sarà

+-----+---------+
|  id |  risolx |
+-- --+---------+
|  1  |   2560  |
|  2  |   1920  |
|  3  |   1600  |
+-----+---------+

Cercare un dato in un campo json

Se vogliamo estrarre i records che abbiano come "browser" il valore "Firefox" utilizziamo questa select utilizzando la funzione JSON_EXTRACT

SELECT id FROM visite WHERE JSON_EXTRACT(specifiche, '$.browser') = "Firefox"

Verranno estratti i soli records 1 e 3.

+----+
| id |
+----+
|  1 |
|  3 |
+----+

Solo il MySQL, puoi utilizzare questa forma alternativa con l'operatore "freccia"

SELECT id FROM visite WHERE specifiche->"$.browser" = "Firefox"

In alternativa a JSON_EXTRACT potremmo usare la funzione JSON_CONTAINS in questo modo, facendo attenzione a cercare il valore con le virgolette

SELECT id FROM visite WHERE JSON_CONTAINS(JSON_EXTRACT(specifiche, '$.browser'),'"Firefox"')

oppure, se non vuoi indicare le virgolette nel valore da ricercare, utilizza la funzione JSON_QUOTE

SELECT id FROM visite WHERE JSON_CONTAINS(JSON_EXTRACT(specifiche, '$.browser'),JSON_QUOTE('Firefox'))

Ed un ulteriore modo per ottenere lo stesso risultato con JSON_CONTAINS è il seguente

SELECT id FROM visite WHERE JSON_CONTAINS(specifiche,JSON_QUOTE('Firefox'),'$.browser')

Vediamo adesso come estrarre tutte le visite di utenti che hanno una risoluzione orizzontale del browser >= 1900 pixel, cioè dobbiamo far riferimento alla chiave "x"

SELECT id FROM visite WHERE JSON_EXTRACT(specifiche, '$.resolution.x') >= 1900 

Il risultato sarà

+----+
| id |
+----+
|  1 |
|  3 |
+----+

In MySQL oramai sappiamo come scrivere la query in modo alternativo

SELECT id FROM visite WHERE specifiche->'$.resolution.x' >= 1900 

Aggiornare un record basandosi su una chiave json

Per aggiornare il valore di una chiave json si utilizza la funzione JSON_SET

Ad esempio, per aggiornare il valore "Safari" con il valore "Chrome" utilizziamo questa query

UPDATE visite 
SET specifiche = JSON_SET(specifiche, '$.browser', 'Chrome') 
WHERE JSON_EXTRACT(specifiche, '$.browser') = "Safari"

In alternativa a JSON_SET è possibile utilizzare allo stesso modo la funzione JSON_REPLACE. Ad esempio aggiorniamo dal valore "Chrome" al valore "Safari"

UPDATE visite
SET specifiche = JSON_REPLACE(specifiche, '$.browser', 'Safari')
WHERE JSON_EXTRACT(specifiche, '$.browser') = "Chrome"

Un altra tipologia di funzione che possiamo utilizzare per aggiornare un campo json è la JSON_REMOVE.

Vogliamo, ad esempio, modificare i nostri record eliminando la chiave "risoluzione" verticale, cioè la chiave "y".

UPDATE visite 
SET specifiche = JSON_REMOVE(specifiche, '$.resolution.y')

Cancellare un record basandosi su una chiave json

Dovreste oramai già essere in grado di cancellare un record basandovi su una chiave json.

Ad esempio, per cancellare tutti i record che hanno come browser "Firefox"

DELETE FROM visite 
WHERE JSON_EXTRACT(specifiche, '$.browser') = "Firefox"

Utilizzo di un array JSON

Vediamo come utilizzare la funzione JSON_ARRAY nel caso in cui volessimo salvare un valore come array json.

Proviamo a modificare i dati inseriti nella tabella visite, dove la chiave "resolution" invece che essere un  oggetto json, lo trasformiamo in un array json (parentesi quadre!!)

INSERT INTO `visite` (`data_accesso`, `pagina`, `specifiche`) VALUES

('2020-10-11 09:52:40', 'servizi', '{ "browser": "Firefox", "os": "Windows", "resolution": [ 2560,1600 ] }'),

('2020-10-12 07:23:12', 'chi siamo', '{ "browser": "Safari", "os": "Mac", "resolution": [ 1920,1080 ] }'),

( '2020-10-12 09:53:21', 'home page', '{ "browser": "Firefox", "os": "Windows", "resolution": [ 1600,900 ] }');

Utilizzando la funzioni JSON_OBJECT E JSON_ARRAY, questo INSERT lo possiamo scrivere così

INSERT INTO `visite` (`data_accesso`, `pagina`, `specifiche`) VALUES

('2020-10-11 09:52:40', 'servizi', JSON_OBJECT('browser','Firefox','os','Windows','resolution', JSON_ARRAY(2560,1600) ) ),

('2020-10-12 07:23:12', 'chi siamo', JSON_OBJECT('browser','Safari','os','Mac','resolution', JSON_ARRAY(1920,1080) ) ),

( '2020-10-12 09:53:21', 'home page', JSON_OBJECT('browser','Firefox','os','Windows','resolution', JSON_ARRAY(1600,900) ) );

Vediamo come estrarre adesso tutte le visite di utenti che hanno una risoluzione orizzontale del browser >= 1900 pixel, cioè dobbiamo far riferimento alla chiave "resolution"

SELECT id FROM visite WHERE JSON_EXTRACT(specifiche, '$.resolution[0]') >= 1900 

Potremmo otterene la risoluzione orizzontale media degli acesso al nostro sito utilizzando la funzione AVG di MySQL, in questo modo

SELECT AVG(JSON_EXTRACT(specifiche, '$.resolution[0]')) AS temp FROM visite

Il risultato è 2026.6666666666667

Si rimanda alla documentazione ufficiale MySQL e MariaDB per tutte le altre funzioni da utilizzare in campi di tipo json.

Indicizzare un campo json

Come abbiamo detto all'inizio di questo articoli, non è possibile creare un indice in un campo json. Tuttavia è possibile seguire una via alternativa creando una "colonna virtuale" su una chiave specifica del campo json, e poi indicizzando questa colonna.

Vediamo, ad esempio, come indicizzare la chiave "browser" del campo json "specifiche".

Occorre innanzitutto, modificare la tabella, ed aggiungere un campo virtuale che chiamiamo, ad esempio, "mybrowser"

ALTER TABLE `visite` ADD mybrowser VARCHAR(50) AS (JSON_VALUE(specifiche, '$.browser'));

E adesso creiamo l'indice su questo campo

ALTER TABLE `visite` ADD INDEX(`mybrowser`);

Così facendo possiamo effettuare una query normale su di un campo indicizzato

SELECT * FROM visite WHERE mybrowser = 'Firefox';

Creato il campo virtuale, possiamo sbizzarrirci con UPDATE

UPDATE visite 
SET specifiche = JSON_SET(specifiche, '$.browser', 'Chrome') 
WHERE mybrowser = 'Safari';

e con DELETE

DELETE FROM visite WHERE mybrowser = 'Firefox';

Approfondimenti

• Documentazione ufficiale MySQL JSON
• Elenco funzioni JSON in MySQL
• Elenco funzioni JSON in MariaDB