Il JSON è un particolare formato di rappresentazione dei dati utilizzato sempre più frequentemente all’interno di svariate tipologie di progetti informatici. Alcuni database non relazionali come MongoDB utilizzano in modo nativo questa rappresentazione dei dati, guarda ad esempio questo articolo sul tema https://www.yimp.it/find-mongodb/. Qui invece vogliamo concentrarci su come interagire con il JSON all’interno del database relazionale SQL Server, sia per effettuare il parsing di dati in input e sia in fase di esportazione.

Parsing di JSON su SQL Server con OpenJson

Iniziamo gli esempi di questa sezione dichiarando su SQL Server una variabile di tipo VARCHAR(MAX) e valorizzandola con una stringa in formato JSON:

DECLARE @json_string VARCHAR(MAX) = '
{"id_fattura":1,
"nome_prodotto":"Prodotto1",
"tipologia":"A",
"importo":120.12,
"data_fattura":"2018-05-01"}';

La prima funzione che studiamo è OpenJson che ci permetterà di trasformare il documento in una tabella:

SELECT *
FROM   OpenJson(@json_string) AS f;

Vediamo il risultato:

Otteniamo una tabella con tre colonne:

• key contenente le chiavi del documento JSON;
• value contenente i rispettivi valori;
• type che è un numero da 0 a 5 e identifica il tipo associato al valore: 0 per i null, 1 per le stringhe, 2 per i numeri, 3 per i booleani, 4 per gli array e 5 per i sotto-documenti.

Tuttavia, per ottenere un output effettivamente lavorabile, possiamo specificare la struttura dell’output tramite l’istruzione WITH, facendo attenzione anche a rispettare i caratteri maiuscoli o minuscoli con cui sono definite le chiavi nel JSON (altrimenti otterrei dei NULL):

SELECT *
FROM   OpenJson(@json_string)
WITH (
  id_fattura INT '$.id_fattura',
  nome_prodotto VARCHAR(50) '$.nome_prodotto',
  tipologia VARCHAR(50) '$.tipologia',
  importo DECIMAL(18,4) '$.importo',
  data_fattura DATE '$.data_fattura'
) AS f;

Facciamo molta attenzione quando utilizziamo il tipo DATETIME in quanto per alcune lingue associate alla sessione (come l’italiano) le date all’interno del JSON saranno interpretate in modo errato se contengo il carattere separatore “-“, come mostrato nella figura in basso. In questo articolo ho approfondito altri temi sul tipo DATETIME https://www.yimp.it/sql-server-filtro-campi-datetime/

Gestire JSON più complessi con Array e sotto-documenti

Vediamo ora come gestire un documento JSON più complesso contenente anche una chiave di tipo array (tipologie) e una di tipo sotto-documento (cliente).

DECLARE @json_string VARCHAR(max) = '
{"id_fattura":1,
"cliente":{"id_cliente": 2,
           "nome":"nicola",
            "cognome":"Iantomasi"},
"tipologie":["A","V"]
}';

In questo caso, se utilizzassi la clausola WITH come prima, otterrei dei NULL in corrispondenza delle chiavi più complesse. Per esse servirà scegliere il tipo NVARCHAR(MAX) e specificare l’espressione AS JSON, come nel codice in basso

SELECT *
FROM   OpenJson(@json_string)
WITH (
 id_fattura INT '$.id_fattura',
 cliente NVARCHAR(MAX) '$.cliente' AS JSON,
 tipologie NVARCHAR(MAX) '$.tipologie' AS JSON
) AS f;

Tuttavia possiamo migliorare il formato dell’output in questo modo:

• per i sotto-documenti si può specificare meglio la struttura dopo il carattere $;
• per gli array possiamo dividere i dati su più righe utilizzando una CROSS APPLY.

SELECT 
 f.id_fattura,
 f.id_cliente,
 f.nome,
 f.cognome,
 t.tipologia
FROM   OpenJson(@json_string)
WITH (
 id_fattura INT '$.id_fattura',
 id_cliente INT '$.cliente.id_cliente',
 nome VARCHAR(50) '$.cliente.nome',
 cognome VARCHAR(50) '$.cliente.cognome',
 tipologia NVARCHAR(MAX) '$.tipologie' AS JSON
) AS f 
CROSS APPLY 
OpenJson(f.tipologia)
WITH (
 tipologia VARCHAR(20) '$') AS  t;

Import su SQL Server di file JSON

Vediamo ora come importare su SQL Server i dati contenuti in un file JSON contenente più documenti, come in questo esempio:

[{"NomeCorso":"Analisi dei dati",
  "NumeroPartecipanti": 6},
 {"NomeCorso":"Python",
  "NumeroPartecipanti": 8},
 {"NomeCorso":"SQL",
  "NumeroPartecipanti": 7}]

Come primo step possiamo utilizzare l’operatore OPENROWSET

SELECT *
FROM   OPENROWSET (BULK 'C:\User\file.json', SINGLE_CLOB) AS JsonFile;

In questo modo otterremo un output con una sola riga nella colonna BulkColumn, con l’intero contenuto del file.

Per portare i dati in un vero formato tabellare scriveremo una CROSS APPLY con la colonna BulkColumn e l’operatore OpenJson descritto nei paragrafi precedenti.

SELECT f.*
FROM   OPENROWSET (BULK 'C:\User\file.json', SINGLE_CLOB) AS JsonFile
CROSS APPLY OpenJson(JsonFile.BulkColumn)
WITH (
 NomeCorso VARCHAR(50) '$.NomeCorso',
 NumeroPartecipanti INT '$.NumeroPartecipanti'
) AS f;

Trasformare una tabella in un JSON

Studiamo ora il processo inverso, cioè passare da una tabella di SQL Server a un documento in formato JSON. Nel caso più semplice in cui interroghiamo una singola tabella, ci basterà utilizzare l’istruzione JSON PATH in fondo alla query. Tramite l’operatore AS possiamo assegnare i nomi delle chiavi alle colonne nella SELECT, con la possibilità di costruire anche una struttura di sotto-documenti scegliendo un’espressione con il carattere punto “.”

--query sql
SELECT TOP 2 
  IdFattura, 
  Importo AS [DatiContabili.importo],
  Iva AS [DatiContabili.iva] 
FROM dbo.Fatture 
FOR JSON PATH;
/* JSON risultante
[{"IdFattura":1,"DatiContabili":{"importo":120.00,"iva":20}},
 {"IdFattura":2,"DatiContabili":{"importo":32.00,"iva":20}}]   
*/

In presenza di NULL, le relative chiavi non verranno create, a meno di specificare la proprietà INCLUDE_NULL_VALUES descritta qui https://learn.microsoft.com/it-it/sql/relational-databases/json/include-null-values-in-json-include-null-values-option?view=sql-server-ver16

Se invece vogliamo combinare i dati presenti su più due tabelle, potremmo utilizzare una subquery all’interno della SELECT per ottenere un documento JSON correttamente strutturato e che segua le relazioni molti a uno nel database.

SELECT
  c.IdCliente,
  (SELECT IdFattura ,
          Importo
   FROM Fatture AS f
   WHERE f.IdCliente = c.IdCliente
   FOR JSON PATH) AS Fatture
FROM Clienti AS c
FOR JSON PATH;

Continua a imparare

• Segui la lezione successiva sui filtri sulla colonna Datetime
• Torna all’indice delle lezioni
• Visita la pagina del mio videocorso avanzato su T-SQL e performance