A partire dalla versione 5.7.8 di MySQL, è disponibile un nuovo tipo di dato non appartenente alle categorie
già esistenti: JSON. Al giorno d'oggi, questo formato testuale è molto diffuso e pertanto non necessita
di grandi presentazioni; ne riassumiamo tuttavia le peculiarità (comunque riassunte su HTML.it):
- è di natura testuale e deriva dal mondo Javascript
- permette di rappresentare aggregazioni di dati in oggetti e di disporre questi ultimi in array e strutture
articolate; - è stato adottato largamente negli ultimi anni dai servizi web (di tipo REST
dati da scambiare ed è il formato privilegiato dei database NoSQL
Proprio questi ultimi campi di impiego hanno reso interessante tale formato nel mondo dei database. MySQL ha
provveduto alla sua integrazione nel proprio set di dati ma non è stato il primo caso del mondo "relazionale" come dimostra, ad esempio,
quanto abbiamo raccontato a proposito di PostgreSQL.
Campi JSON nelle tabelle
Per sperimentare questa nuova funzionalità dobbiamo innanzitutto inserirla in una tabella. Possiamo procedere
con strumenti visuali come PhpMyAdmin, che già ne prevede la possibilità:
o agendo via SQL come nel seguente esempio:
CREATE TABLE `esempio_db`.`candidati`
( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`nome` VARCHAR(50) NOT NULL ,
`cognome` VARCHAR(50) NOT NULL ,
`esperienze` JSON NOT NULL ,
PRIMARY KEY (`id`))
ENGINE = InnoDB;Abbiamo usato il tipo JSON per l'ultimo campo, dove vogliamo riassumere le diverse esperienze lavorative che hanno contrassegnato la carriera
di un potenziale candidato ad un posto di lavoro. L'utilità di questo genere di dati può essere proprio questa: offrire un
modo elastico ma pur sempre strutturato
Inserimento di dati
Con la prima operazione di INSERT possiamo sperimentare subito questo tipo di dato:
INSERT INTO `candidati`
(nome, cognome, esperienze)
VALUES('Sergio', 'Neri',
'[{"incarico":"programmatore", "settore":"Java", "inizio":2008, "fine":2013, "azienda":"ABC s.r.l."},
{"incarico":"docente", "materia":"C++", "inizio":2009, "fine":2009, "istituto":"StudiareInformatica s.n.c."}]')L'inserimento andrà così a buon fine, ma un aspetto fondamentale è scrivere del JSON corretto rispettando la
struttura che devono avere array ed oggetti. Se si osservano i due oggetti JSON, si vede che non hanno campi identici perchè le due attività svolte sono di natura differente.
Per evitare di scrivere manualmente i dati JSON, esistono delle funzioni apposite:
JSON_OBJECT: riceve una sequenza di valori che servono a costruire un oggetto svolgendo alternatamente il ruolo
di chiave e di relativo valore. Esempio: creiamo un oggetto JSON che contempli le proprietà incarico settoreSELECT JSON_OBJECT('incarico','programmatore','settore','Cobol'){"settore": "Cobol", "incarico": "programmatore"}JSON_ARRAYSELECT JSON_ARRAY( JSON_OBJECT('incarico','programmatore','settore','Cobol'), JSON_OBJECT('incarico','programmatore','settore','Java') )[{"settore": "Cobol", "incarico": "programmatore"}, {"settore": "Java", "incarico": "programmatore"}]
Grazie a queste funzioni, avremmo potuto produrre l'inserimento visto in precedenza senza dover curare manualmente
il testo in JSON:
INSERT INTO `candidati`
(nome, cognome, esperienze)
VALUES('Sergio', 'Neri',
JSON_ARRAY(
JSON_OBJECT('incarico','programmatore','settore','Java','inizio',2008,'fine',2013, 'azienda','ABC s.r.l.'),
JSON_OBJECT('incarico','docente','materia','C++','inizio',2009,'fine',2009, 'istituto','StudiareInformatica s.n.c.')
))
Ricerche in un campo JSON
L'aspetto cui dobbiamo dedicare un pò più di attenzione è la lettura dei dati. Nel classico lavoro su SQL, è
sufficiente indicare di quali campi vogliamo visualizzare il valore, ma in questo caso il JSON crea una struttura
interna al campo che deve essere navigata opportunamente con l'operatore freccia (->).
percorso da seguire all'interno di array ed oggetti. Per estrarre il primo oggetto
usare una SELECT
SELECT esperienze->"$[0]" FROM `candidati`
Verrà restituito in output il primo oggetto di ogni array incluso nel campo esperienze. Per il resto, la query
può essere completata nel modo che ben conosciamo con clausole WHERE o altri campi nella proiezione. Tale interrogazione
ha successo anche se non tutti i campi contengono un array di oggetti, ovvero anche in quei casi in cui ci sono singoli oggetti.
Con il punto
.
SELECT esperienze->"$[0].inizio" FROM `candidati`
grazie alla quale otterremmo un elenco di numeri rappresentanti altrettante annualità.
Qualora l'oggetto JSON esaminato non disponesse della proprietà richiesta verrebbe restituito, per quel record,
un valore NULL
Analogamente, tale stile di navigazione potrebbe essere applicato ai campi JSON qualora fossero coinvolti in una
clausola WHERE
proprietà fine
SELECT * FROM `candidati` WHERE esperienze->"$[0].fine" IS NULL
Considerazioni finali
È bene sottolineare che i campi JSON vanno utilizzati con parsimonia. Sebbene essi offrano il vantaggio di evadere dallo stile strutturato, le operazioni di parsing che richiedono possono risultare onerose e rallentare le nostre query.
È pur vero che questo tipo di dati ci avvicina a realtà moderne, che possono richiedere il salvataggio di dati JSON, per esempio ottenuti da un servizio REST. Resta comunque il fatto che, ove possibile, è bene inserire informazioni sfruttando i tipi di dato canonici, al fine di sfruttare le potenzialità di analisi e sintesi insite nel linguaggio SQL.
Ti consigliamo anche