Utilizzare FOR JSON per esportare i dati da SQL Server in formato JSON.
Sta diventando ricorrente l’utilizzo di strumenti per trattare dati non in formato rigido: ovvero manipolare e trattare il formato JSON. Ecco perché da relativamente poco tempo sistemi come SQLServer o PostgreSQL implementano funzioni e metodi per manipolare, leggere e trasformare il formato JSON.
In questo post utilizzeremo l’istruzione FOR JSON per estrarre mediante apposita query i dati di una tabella di SQL Server e visualizzare l’output in formato JSON.
Successivamente il file json potrà essere utilizzato per eseguire le operazioni CRUD tramite API.
Esistono due modi in cui i risultati relazionali possono essere convertiti in JSON, ovvero le opzioni AUTO e PATH.
Sommario
Utilizzare FOR JSON in Sql Server con la modalità AUTO
Per formattare l’output della clausola FOR JSON automaticamente in base alla struttura dell’istruzione SELECT occorre specificare l’opzione AUTO.
Quando si specifica l’opzione AUTO il formato dell’output JSON viene determinato automaticamente in base all’ordine delle colonne nell’elenco SELECT e delle relative tabelle di origine. Non è possibile modificare questo formato.
L’alternativa consiste nell’usare l’opzione PATH per mantenere il controllo sull’output che vedremo successivamente.
Una query che usa l’opzione FOR JSON AUTO deve avere una clausola FROM .
Di seguito sono riportati alcuni esempi della clausola FOR JSON con l’opzione AUTO
Esempi FOR JSON AUTO
Quando una query fa riferimento a una sola tabella, i risultati della clausola FOR JSON AUTO generano come output alias separati da punti (come Info.MiddleName nell’esempio seguente) come chiavi con punti e non come oggetti annidati.
SELECT TOP 5
BusinessEntityID As Id,
FirstName, LastName,
Title As 'Info.Title',
MiddleName As 'Info.MiddleName'
FROM Person.Person
FOR JSON AUTORisultato:
[{
"Id": 1,
"FirstName": "Ken",
"LastName": "Sánchez",
"Info.MiddleName": "J"
}, {
"Id": 2,
"FirstName": "Terri",
"LastName": "Duffy",
"Info.MiddleName": "Lee"
}, {
"Id": 3,
"FirstName": "Roberto",
"LastName": "Tamburello"
}, {
"Id": 4,
"FirstName": "Rob",
"LastName": "Walters"
}, {
"Id": 5,
"FirstName": "Gail",
"LastName": "Erickson",
"Info.Title": "Ms.",
"Info.MiddleName": "A"
}]Da tenere in considerazione che i campi che hanno valore Null non vengono mostrari nell’output. Tuttavia, è possibile mostrarli utilizzando il comando INCLUDE_NULL_VALUES nella clausola FOR.
Esempio: più tabelle
Quando si uniscono più tabelle tramite JOIN, le colonne della prima tabella vengono generate come proprietà dell’oggetto radice. Le colonne della seconda tabella vengono generate come proprietà di un oggetto annidato. Il nome della tabella o l’alias della seconda tabella (ad esempio D nell’esempio seguente) viene usato come nome della matrice annidata.
SELECT TOP 2 SalesOrderNumber,
OrderDate,
UnitPrice,
OrderQty
FROM Sales.SalesOrderHeader H
INNER JOIN Sales.SalesOrderDetail D
ON H.SalesOrderID = D.SalesOrderID
FOR JSON AUTORisultato:
[{
"SalesOrderNumber": "SO43659",
"OrderDate": "2011-05-31T00:00:00",
"D": [{
"UnitPrice": 24.99,
"OrderQty": 1
}]
}, {
"SalesOrderNumber": "SO43659",
"D": [{
"UnitPrice": 34.40
}, {
"UnitPrice": 134.24,
"OrderQty": 5
}]
}]
Utilizzare FOR JSON in Sql Server con la modalità PATH
Per mantenere il controllo completo sull’output della clausola FOR JSON, specificare l’opzione PATH.
La modalità PATH consente di creare oggetti wrapper e annidare proprietà complesse. I risultati vengono formattati sotto forma di matrice di oggetti JSON.
Di seguito sono riportati alcuni esempi della clausola FOR JSON con l’opzione PATH . Per formattare risultati annidati, usare nomi di colonna separati da punti oppure query annidate, come illustrato negli esempi seguenti.
Per impostazione predefinita, i valori null non vengono inclusi nell’output FOR JSON.
Esempio: nomi di colonna separati da punti
La query seguente formatta le prime cinque righe della tabella Person di AdventureWorks come JSON.
La clausola FOR JSON PATH usa l’alias o il nome di colonna per determinare il nome della chiave nell’output JSON. Se un alias contiene punti, l’opzione PATH crea oggetti annidati.
SELECT TOP 5
BusinessEntityID As Id,
FirstName, LastName,
Title As 'Info.Title',
MiddleName As 'Info.MiddleName'
FROM Person.Person
FOR JSON PATHRisultato:
[{
"Id": 1,
"FirstName": "Ken",
"LastName": "Sanchez",
"Info": {
"MiddleName": "J"
}
}, {
"Id": 2,
"FirstName": "Terri",
"LastName": "Duffy",
"Info": {
"MiddleName": "Lee"
}
}, {
"Id": 3,
"FirstName": "Roberto",
"LastName": "Tamburello"
}, {
"Id": 4,
"FirstName": "Rob",
"LastName": "Walters"
}, {
"Id": 5,
"FirstName": "Gail",
"LastName": "Erickson",
"Info": {
"Title": "Ms.",
"MiddleName": "A"
}
}]Esempio: più tabelle
Se nella query si fa riferimento a più tabelle, FOR JSON PATH annida ogni colonna usando il relativo alias. La query seguente crea un oggetto JSON per ogni coppia (OrderHeader,OrderDetails) unita in join nella query.
SELECT TOP 2 H.SalesOrderNumber AS 'Order.Number',
H.OrderDate AS 'Order.Date',
D.UnitPrice AS 'Product.Price',
D.OrderQty AS 'Product.Quantity'
FROM Sales.SalesOrderHeader H
INNER JOIN Sales.SalesOrderDetail D
ON H.SalesOrderID = D.SalesOrderID
FOR JSON PATHRisultato;
[{
"Order": {
"Number": "SO43659",
"Date": "2011-05-31T00:00:00"
},
"Product": {
"Price": 2024.9940,
"Quantity": 1
}
}, {
"Order": {
"Number": "SO43659"
},
"Product": {
"Price": 2024.9940
}
}]
Conclusione
Abbiamo visto come utilizzare FOR JSON in Sql Server; questi sono solo alcuni esempi di utilizzo, ma come vedi è fondamentale sfruttare fino in fondo le potenzialità offerte anche dai database relazionali e non relegarli a meri sistemi di storage.
Ahimè, non è un argomento semplicissimo ma con un’pò d’impegno è possibile ottenere ottimi risultati.
Con la tecnologia che richiede sistemi di scambio dati più snelli e duttili, devi assolutamente provare le nuove potenzialità offerte dai database attuali perché potrebbero semplificarti davvero il lavoro.
Una di queste è la capacità di gestire il formato JSON come un formato di prima classe per SQLServer (ma come ti ho detto altri sistemi non sono da meno, come PostgreSQL). Ma non è l’unica.
