Come utilizzare i campi di dati JSON nei database MySQL

by Janeth Kent Date: 12-06-2019 mysql nosql json

Ultimamente abbiamo potuto notare come la linea tra i database SQL e NoSQL è diventata sempre più sottile e confusa con ogni sistema che adotta le caratteristiche dell'altro. I database MySQL 5.7 InnoDB e PostgreSQL 9.4 supportano entrambi direttamente i tipi di documenti JSON in un unico campo. In questo articolo, esamineremo più in dettaglio l'implementazione JSON di MySQL.

(PostgreSQL ha supportato JSON prima della versione 9.4 e qualsiasi database accetterà i documenti JSON come un singolo stringa blob. Tuttavia, MySQL e PostgreSQL ora supportano direttamente i dati JSON validati in coppie chiave/valore reale piuttosto che in una semplice stringa.)

Solo perché è possibile memorizzare JSON non vuol dire che tu lo debba fare per forza!

La normalizzazione è una tecnica utilizzata per ottimizzare la struttura del database. La regola del First Normal Form (1NF) regola che ogni colonna deve contenere un singolo valore - che viene interrotto dalla memorizzazione di documenti JSON a più valori.

Se si hanno chiari requisiti per i dati relazionali, allora é meglio utilizzare campi a valore singolo appropriati. JSON dovrebbe essere usato con parsimonia come ultima risorsa. I campi dei valori JSON non possono essere indicizzati, quindi evitate di usarlo su colonne che vengono aggiornate o ricercate regolarmente. Inoltre, poche applicazioni client supportano JSON e la tecnologia è più recente e forse meno stabile rispetto ad altre tipologie.

Detto questo, ci sono buoni casi d'uso di JSON per dati scarsamente popolati o attributi personalizzati.

Creare una tabella con un campo JSON

Si consideri un negozio che vende libri. Una tabella del libro avrà un ID, ISBN, titolo, editore, numero di pagine e altri dati relazionali che si applica a tutti i libri. Presumiamo di voler aggiungere un numero qualsiasi di tag di categoria a qualsiasi libro. Potremmo raggiungere questo obiettivo in SQL utilizzando:

una tabella di tag che ha memorizzato ogni nome di tag contro un ID univoco, e
una tabella tagmap con ID dei libri di mappatura dei record da molti a molti record a ID dei tag

Funzionerà, ma è un lavoro ingombrante e considerevole per una caratteristica minore. Pertanto, definiremo un campo JSON per i tags nella tabella del nostro database MySQL:

CREATE TABLE `libro` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `titolo` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Si noti che le colonne JSON non possono avere un valore predefinito, essere usate come chiave primaria, essere referenziate come chiave esterna o avere un indice. È possibile creare indici secondari sulle colonne virtuali generate, ma è forse più facile mantenere un valore indicizzato in un campo separato.

Aggiunta di dati JSON

Interi documenti JSON possono essere passati in dichiarazioni INSERT o UPDATE. Per esempio, i nostri libro tag possono essere passati come array:

INSERT INTO `libro` (`titolo`, `tags`)
VALUES (
  'ECMAScript 2015: A ManoWeb Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

JSON può anche essere creato con:

la funzione JSON_ARRAY() che crea array, ad esempio.:

-- restituisce [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, 'abc');

la funzione JSON_OBJECT() che crea oggetti, ad esempio:

-- restituisce {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);

La funzione JSON_MERGE() per unire documenti, ad esempio:

-- restituisce ["a", 1, {"key": "value"}]:
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');

oppure è possibile effettuare (CAST anyValue AS JSON).

La funzione JSON_TYPE() consente di controllare i tipi di valori JSON. Dovrebbe restituire OBJECT, ARRAY o un errore, ad es.

-- restituisce ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- restituisce OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- restituisce an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');

Allo stesso modo, la funzione JSON_VALID() restituisce 1 quando JSON è valido:

-- restituisce 1:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- restituisce 1:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- restituisce 0:
SELECT JSON_TYPE('{"a": 1, "b": 2');

Il tentativo di inserire un documento JSON non valido genera un errore e l'intero record non viene inserito/aggiornato.

Ricerca dei dati JSON

La funzione JSON_CONTAINS() accetta il documento JSON ricercato e un altro da confrontare. Restituisce 1 quando viene trovata una corrispondenza, ad es.

-- all libros with the 'JavaScript' tag:
SELECT * FROM `libro` 
WHERE JSON_CONTAINS(tags, '["JavaScript"]');

La funzione simile JSON_SEARCH() restituisce il percorso alla corrispondenza data o NULL quando non c'è corrispondenza. Viene passato il documento JSON che viene cercato, 'one' per trovare la prima corrispondenza o 'all' per trovare tutte le corrispondenze, e una stringa di ricerca, ad es.

-- all libros with tags starting 'Java':
SELECT * FROM `libro` 
WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

JSON Paths

Un percorso JSON ha come obiettivo i valori e può essere utilizzato per estrarre o modificare parti di un documento. La funzione JSON_EXTRACT() lo dimostra estraendo uno o più valori:

-- restituisce "ManoWeb":
SELECT JSON_EXTRACT(
  '{"id": 1, "sitoweb": "ManoWeb"}', 
  '$.sitoweb'
);

Tutte le definizioni dei percorsi JSON iniziano con un $ seguito da altri selettori:

un periodo seguito da un nome, ad esempio $.website [N] dove N è la posizione in un array indicizzato a zero.
un carattere jolly .[*] che valuta tutti i membri di un oggetto
un carattere jolly [*] che valuta tutti i membri di una matrice
un prefisso ** suffisso jolly che valuta tutti i percorsi che iniziano con il prefisso nominato e terminano con il suffisso nominato.

Facendo riferimento a questo documento JSON:

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

i seguenti percorsi:

$.a restituisce 1
$.c restituisce [3, 4]
$.c[1] restituisce 4
$.d.d.e. restituisce 5
$**.e restituisce [5]

Estrarre i percorsi JSON nelle query

Possiamo estrarre il primo tag dalla nostra tabella libro usando la query:

SELECT
  nome,
  tags->"$[0]" AS `tag1`
FROM `libro`;

Per un esempio più complesso, supponiamo di avere una tabella utenti con dati di profilo JSON, ad esempio:

id	nome	profilo
1	Luigi	{ “twitter”: “@holyguard”, “facelibro”: “holyguard” }
2	ManoWeb	{ “twitter”: “@manoweb” }

Possiamo estrarre il nome Twitter utilizzando un percorso JSON, ad esempio

SELECT
  nome, profilo->"$.twitter" AS `twitter`
FROM `utenti`;

Possiamo anche fare riferimento a un percorso JSON nella clausola WHERE per restituire solo gli utenti con un account Twitter:

SELECT
  nome, profilo->"$.twitter" AS `twitter`
FROM `utenti`
WHERE
  profilo->"$.twitter" IS NOT NULL;

Modifica di una parte di un documento JSON

Ci sono diverse funzioni MySQL per modificare parti di un documento JSON usando la notazione del percorso. Queste includono:

JSON_SET(doc, path, val[, path, val]....) - inserisce o aggiorna i dati nel documento
JSON_INSERT(doc, path, val[, path, val]....) - inserisce i dati nel documento
JSON_REPLACE(doc, path, val[, path, val]....) - sostituisce i dati del documento
JSON_MERGE(doc, doc[, doc[, doc].....) - fonde due o più documenti
JSON_ARRAY_APPEND(doc, path, val[, path, val]....) - aggiunge valori alla fine di un array di valori
JSON_ARRAY_INSERT(doc, path, val[, path, val]....) - inserisce un array all'interno del documento
JSON_REMOVE(doc, path[, path].....) - rimuove i dati dal documento.

Possiamo quindi aggiungere un tag 'programmazione' a qualsiasi libro che abbia già un tag 'JavaScript':

UPDATE `libro`
  SET tags = JSON_MERGE(tags, '["programmazione"]')
WHERE
  JSON_SEARCH(tags, 'one', 'programmazione') IS NULL AND
  JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

Ulteriori informazioni

Il manuale MySQL fornisce ulteriori informazioni sul tipo di dati JSON e sulle relative funzioni JSON.

Ancora una volta, vi invito a non usare JSON a meno che non sia assolutamente necessario. Si potrebbe emulare un intero database NoSQL orientato ai documenti in MySQL, ma ciò negherebbe molti vantaggi di SQL, e si potrebbe anche passare a un vero sistema NoSQL. Detto questo, i tipi di dati JSON potrebbero risparmiare qualche sforzo per requisiti di dati più oscuri all'interno di un'applicazione basata su SQL.