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
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT
[ProductID],
[ProductNumber],
[Name],
[ListPrice]
FROM
[Production].[Product]
WHERE
[ListPrice] > 0
AND
[ProductID] BETWEEN 940 AND 942
FOR JSON AUTO
|
Majd a kimenete, ahová a sortöréseket én tettem bele a könnyebb olvashatóság kedvéért:
1 2 3 4 5 | [
{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900},
{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900},
{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900}
]
|
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á:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT
P.[ProductID],
P.[ProductNumber],
P.[Name],
P.[ListPrice],
PM.[Name] AS [ProductModelName]
FROM
[Production].[Product] P
JOIN
[Production].[ProductModel] PM ON P.[ProductModelID] = PM.[ProductModelID]
WHERE
[ListPrice] > 0
AND
[ProductID] BETWEEN 940 AND 942
FOR JSON AUTO
|
A kimenet, ahol a sortöréseket ismét én adtam hozzá:
1 2 3 4 5 | [
{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900,"PM":[{"ProductModelName":"HL Road Pedal"}]},
{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900,"PM":[{"ProductModelName":"Touring Pedal"}]},
{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900,"PM":[{"ProductModelName":"ML Mountain Frame-W"}]}
]
|
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:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT
[ProductID],
[ProductNumber],
[Name],
[ListPrice]
FROM
[Production].[Product]
WHERE
[ListPrice] > 0
AND
[ProductID] BETWEEN 940 AND 942
FOR JSON PATH
|
Ennek a kimenete pont ugyan az lesz, mint az AUTO esetben :)
1 2 3 4 5 | [
{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900},
{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900},
{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900}
]
|
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT
P.[ProductID],
P.[ProductNumber],
P.[Name],
P.[ListPrice],
PM.[Name] AS [ProductModelName]
FROM
[Production].[Product] P
JOIN
[Production].[ProductModel] PM ON P.[ProductModelID] = PM.[ProductModelID]
WHERE
[ListPrice] > 0
AND
[ProductID] BETWEEN 940 AND 942
FOR JSON PATH
|
Lássuk a kimenetet:
1 2 3 4 5 | [
{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900,"ProductModelName":"HL Road Pedal"},
{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900,"ProductModelName":"Touring Pedal"},
{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900,"ProductModelName":"ML Mountain Frame-W"}
]
|
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT
[ProductID],
[ProductNumber],
[Name],
[ListPrice],
[Class]
FROM
[Production].[Product]
WHERE
[ListPrice] > 0
AND
[ProductID] BETWEEN 940 AND 942
FOR JSON AUTO, ROOT('ProductList')
|
Figyeljük meg a kimenetben, hogy a Class attribútum nem jelenik meg a ProductID=941 esetén
1 2 3 4 5 | {"ProductList":[
{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900,"Class":"H "},
{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900},
{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900,"Class":"M "}
]}
|
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT
[ProductID],
[ProductNumber],
[Name],
[ListPrice],
[Class]
FROM
[Production].[Product]
WHERE
[ListPrice] > 0
AND
[ProductID] BETWEEN 940 AND 942
FOR JSON AUTO, ROOT('ProductList'), INCLUDE_NULL_VALUES
|
Látható, hogy a kimenetben ott van a null érték a ProductID=941 elemnél:
1 2 3 4 5 | {"ProductList":[
{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900,"Class":"H "},
{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900,"Class":null},
{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900,"Class":"M "}
]}
|
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 2 3 4 5 | {"ProductList":[
{"ProductID":940,"ProductNumber":"PD-R853","Name":"HL Road Pedal","ListPrice":80.9900},
{"ProductID":941,"ProductNumber":"PD-T852","Name":"Touring Pedal","ListPrice":80.9900},
{"ProductID":942,"ProductNumber":"FR-M63S-38","Name":"ML Mountain Frame-W - Silver, 38","ListPrice":364.0900}
]}
|
Azaz, szeretném, ha a tömböm ProductList névre hallgatna: a ROOT záradék hozzáadásával ez is megoldható:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT
[ProductID],
[ProductNumber],
[Name],
[ListPrice]
FROM
[Production].[Product]
WHERE
[ListPrice] > 0
AND
[ProductID] BETWEEN 940 AND 942
FOR 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.