Come gestire il formato JSON su SQL Server

/ SQL e Database
JSON su SQL Server

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:

output openjson su sql server

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;

utilizzo clausola with con openjson

 

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/

json e datetime

Il tuo percorso completo di Analisi dei Dati

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;

utilizzo as json per gestire array e sotto-documenti

 

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;

output cross apply e json

T-SQL avanzato e tuning delle query

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.

output openrowset per importare file json

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;

output di query sql con openrowset e cross apply con openjson

 

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;
output for json path con query annidata

Impara ad analizzare i dati con MongoDB

Conclusioni su JSON e SQL Server

In questo articolo abbiamo visto come utilizzare il linguaggio T-SQL per passare dal formato JSON al formato tabellare e viceversa. Abbiamo utilizzato principalmente le istruzioni OpenJson e OPEWROWSET e FOR JSON PATH, eventualmente combinate con la CROSS APPLY. Per la mia esperienza questa sintassi non è ancora molto diffusa tra gli sviluppatori T-SQL, nonostante si tratti di un tema molto importante vista la rapida diffusione del formato JSON.

Se vuoi approfondire altri temi avanzati della programmazione T-SQL e le performance di SQL Server puoi visitare la pagina del mio video-corso https://www.yimp.it/video-corso-t-sql-avanzato-tuning-query/

Scroll to Top
Torna su