SQL Server 2016 - JSON
Hamarosan megjelenik a publikus CTP is az SQL 2016-ból, de addig is lássunk pár újdonságot részletesen is. Ebben a bejegyzésben a JSON támogatásról lesz szó.
FONTOS: az itt leírtak a publikus és/vagy az RTM verzióban nem vagy másképpen működhetnek!
A JSON formátum egy igen kedvelt megoldás adatcsere, adattovábbítás esetén. Őszinte leszek, én nem vagyok híve ennek, sőt mások sem nagyon. Eddig a JSON formátum beolvasása és generálása csak mindenféle extra helper függvény segítségével volt lehetséges (pl. ezzel). A T-SQL nyelv nem(sem) mindenféle szöveg manipulálásra van kitalálva, ahogy Phil Factor írta :):
"Rekurzió használata a T-SQL nyelvben olyan, mint amikor szumó harcosok balettoznak. Lehetséges ugyan, de nem túl szép látvány."
Ettől függetlenül egy rég várt feature és végre van natív lehetőség JSON-t generálni. Sokkal hatékonyabban tárol adatokat, mint mondjuk az XML, ahol a nyitó és záró tag-ek is helyet foglalnak. Alapvetően itt is ugyan azok a szabályok érvényesek: csak annyi adatot (sort és oszlopot) kérdezzünk le, amennyire valójában szükségünk van, ne akarjuk az egész adatbázist JSON formátumban legenerálni és azt átadni az alkalmazásoknak, nem lesz hatékony!
Egyelőre a tárolás csak szöveges típusként lehetséges (jelenleg), azonban a relációs modellből nagyon könnyen tudunk JSON formátumot gyártani a FOR JSON záradékkal.
FOR JSON AUTO | PATH
Alapvetően egy JSON kiment létrehozása szinte ugyan az, mint az XML esetében: a lekérdezés végére oda kell biggyeszteni, hogy FOR JSON, majd hogy milyen formában legyen ez megvalósítva. Erre két lehetőségünk van: AUTO vagy PATH.
AUTO
Ebben az esetben automatikusan megformázza a lekérdezésünket JSON formátumra, pl: termékek azonosítója, neve és lista ára
1SELECT
2 [ProductID],
3 [ProductNumber],
4 [Name],
5 [ListPrice]
6FROM
7 [Production].[Product]
8WHERE
9 [ListPrice] > 0
10AND
11 [ProductID] BETWEEN 940 AND 942
12FOR JSON AUTO
Majd a kimenete, ahová a sortöréseket én tettem bele a könnyebb olvashatóság kedvéért:
1[
2{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900},
3{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900},
4{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900}
5]
Látható, hogy a kimenet egy tömböt adott vissza három elemmel, aminek a ProductID, ProiductNumber, Name és ListPrice attribútumokkal rendelkezik. Azonban, amikor már egy másik táblát is beveszünk a lekérdezésbe, kicsit változik a kép: most a lekérdezésben nem csak a termék információk, de a termék modell neve is szerepelni fog, amit egy JOIN segítségével adok hozzá:
1SELECT
2 P.[ProductID],
3 P.[ProductNumber],
4 P.[Name],
5 P.[ListPrice],
6 PM.[Name] AS [ProductModelName]
7FROM
8 [Production].[Product] P
9JOIN
10 [Production].[ProductModel] PM ON P.[ProductModelID] = PM.[ProductModelID]
11WHERE
12 [ListPrice] > 0
13AND
14 [ProductID] BETWEEN 940 AND 942
15FOR JSON AUTO
A kimenet, ahol a sortöréseket ismét én adtam hozzá:
1[
2{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900,"PM":[{"ProductModelName":"HL Road Pedal"}]},
3{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900,"PM":[{"ProductModelName":"Touring Pedal"}]},
4{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900,"PM":[{"ProductModelName":"ML Mountain Frame-W"}]}
5]
Itt vegyük észre, hogy a JOIN során használt alias bekerült a ProductModel tábla esetén, PM, ami egy külön, beágyazott tömbként szerepel.
PATH
Most nézzük meg a fentieket a PATH esetén. Az első eset ismét csak egy táblát kérdez le és alakítja JSON formátumra:
1SELECT
2 [ProductID],
3 [ProductNumber],
4 [Name],
5 [ListPrice]
6FROM
7 [Production].[Product]
8WHERE
9 [ListPrice] > 0
10AND
11 [ProductID] BETWEEN 940 AND 942
12FOR JSON PATH
Ennek a kimenete pont ugyan az lesz, mint az AUTO esetben :)
1[
2{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900},
3{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900},
4{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900}
5]
Igen ám, de akkor mi értelme van? Nézzük meg JOIN esetén hogyan néz ki a dolog: ismét hozzáteszem a ProductModel táblát.
1SELECT
2 P.[ProductID],
3 P.[ProductNumber],
4 P.[Name],
5 P.[ListPrice],
6 PM.[Name] AS [ProductModelName]
7FROM
8 [Production].[Product] P
9JOIN
10 [Production].[ProductModel] PM ON P.[ProductModelID] = PM.[ProductModelID]
11WHERE
12 [ListPrice] > 0
13AND
14 [ProductID] BETWEEN 940 AND 942
15FOR JSON PATH
Lássuk a kimenetet:
1[
2{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900,"ProductModelName":"HL Road Pedal"},
3{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900,"ProductModelName":"Touring Pedal"},
4{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900,"ProductModelName":"ML Mountain Frame-W"}
5]
Ahham :), szóval nem külön tömbként, hanem egy tömb elemének attribútumaként jelenik meg így a ProductModelName. Lehetne ezt a végletekig csűrni-csavarni, de ezt majd innentől mindenki próbálgassa maga, amint elérhető a CTP ;)
Van további pár dolog, amiről viszont beszélni kell még: NULL értékek kezelése, root elem hozzáadása.
NULL érték kezelése
Alapvetően a JSON formátumban nem jelennek meg azok az attribútumok, ahol az adatbázisban az érték NULL. Ez lehet probléma is, illetve nem is. Attól függően, hogyan kezeli az alkalmazás, szükségünk lehet a NULL érték megjelenítésére is. Lássuk is erre is egy példát: a Product táblában a Class oszlopnál előfordulhat, hogy NULL értéket tartalmaz.
1SELECT
2 [ProductID],
3 [ProductNumber],
4 [Name],
5 [ListPrice],
6 [Class]
7FROM
8 [Production].[Product]
9WHERE
10 [ListPrice] > 0
11AND
12 [ProductID] BETWEEN 940 AND 942
13FOR JSON AUTO, ROOT('ProductList')
Figyeljük meg a kimenetben, hogy a Class attribútum nem jelenik meg a ProductID=941 esetén
1{"ProductList":[
2{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900,"Class":"H "},
3{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900},
4{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900,"Class":"M "}
5]}
Igen ám, de mi van akkor, ha kell nekem a NULL érték? Van erre is megoldás: be kell írni az INCLUDE_NULL_VALUES-t a FOR JSON záradékba:
1SELECT
2 [ProductID],
3 [ProductNumber],
4 [Name],
5 [ListPrice],
6 [Class]
7FROM
8 [Production].[Product]
9WHERE
10 [ListPrice] > 0
11AND
12 [ProductID] BETWEEN 940 AND 942
13FOR JSON AUTO, ROOT('ProductList'), INCLUDE_NULL_VALUES
Látható, hogy a kimenetben ott van a null érték a ProductID=941 elemnél:
1{"ProductList":[
2{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900,"Class":"H "},
3{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900,"Class":null},
4{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900,"Class":"M "}
5]}
ROOT elem hozzáadása
Nem lesz meglepetés :), aki használta már az SQLXML-t ismerős lehet a megoldás. Az alábbi kimenetet szeretném elérni:
1{"ProductList":[
2{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900},
3{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900},
4{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900}
5]}
Azaz, szeretném, ha a tömböm ProductList névre hallgatna: a ROOT záradék hozzáadásával ez is megoldható:
1SELECT
2 [ProductID],
3 [ProductNumber],
4 [Name],
5 [ListPrice]
6FROM
7 [Production].[Product]
8WHERE
9 [ListPrice] > 0
10AND
11 [ProductID] BETWEEN 940 AND 942
12FOR JSON AUTO, ROOT('ProductList')
Remélem sikerült egy kis ízelítőt adni erről a funkcióról is, hamarosan még érdekesebbekkel jövök.