Il tipo di dato JSON

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 specialmente) per la formulazione dei
  dati da scambiare ed è il formato privilegiato dei database NoSQL per la memorizzazione delle informazioni.

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 per rappresentare informazioni in tutta la loro varietà. Ciò che serve è
avere strumenti per manipolarle: vediamo cosa offre MySQL.

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 e settore valorizzate
  rispettivamente alle stringhe 'programmatore' e 'Cobol':
  

  SELECT JSON_OBJECT('incarico','programmatore','settore','Cobol')

  
  Eccone l'output:
  

  {"settore": "Cobol", "incarico": "programmatore"}

• JSON_ARRAY: in maniera analoga, crea un array JSON a partire dagli elementi forniti in input. Creiamo, ad esempio,
  un array di due oggetti dalla struttura simile a quello visto al punto precedente:
  

  SELECT JSON_ARRAY(
       JSON_OBJECT('incarico','programmatore','settore','Cobol'),
  	 JSON_OBJECT('incarico','programmatore','settore','Java')
  	 )

  
  per ottenere questo risultato:
  

  [{"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 (->).

L'operatore freccia segue il nome del campo JSON ed è seguito da un'espressione tra doppi apici che rappresenta un
percorso da seguire all'interno di array ed oggetti. Per estrarre il primo oggetto presente in un array possiamo
usare una SELECT di questo tipo:

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 (.) si può proseguire la navigazione all'interno dell'oggetto specificando una proprietà interna.
Se ad esempio volessimo ottenere l'anno di inizio dell'attività presente all'interno del primo oggetto, potremmo
utilizzare la seguente query:

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 senza alcun errore.

Analogamente, tale stile di navigazione potrebbe essere applicato ai campi JSON qualora fossero coinvolti in una
clausola WHERE. Immaginando che gli oggetti indicanti attività non ancora concluse non disponessero affatto di una
proprietà fine nella propria struttura, potremmo cercare tutti i candidati con un'attività lavorativa tuttora in
essere nel seguente modo:

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.