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
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:
[
{"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á:
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á:
[
{"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:
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 :)
[
{"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.
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:
[
{"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. 

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
{"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:
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:
{"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:
{"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ó:
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.

Comments (2) -

Kovacs Zsolt 5/27/2015 8:20:59 PM

Köszi a postot Jani!
Reméltem, hogy a JSON támogatás abban testesül meg, hogy lesz json típus, de ahogy elnézem arra még várni kell. Így még mindig keresnem kell a jó indokot, hogy miért is érné meg majd bevezetni Smile

Berke János 5/27/2015 8:35:44 PM

Egyelőre nem is baj szerintem, mert akkor minden táblának egy oszlopa lenne, aminek a típusa JSON lenne a neve pedig Everything Smile

Add comment