Bevezetés az Azure SQL Database és az Azure SQL Managed Instance JSON-funkcióinak használatába
A következőre vonatkozik: Azure SQL DatabaseFelügyelt Azure SQL-példány
Az Azure SQL Database és a felügyelt Azure SQL-példány lehetővé teszi a JavaScript Object Notation (JSON) formátumban képviselt adatok elemzését és lekérdezését, valamint a relációs adatok JSON-szövegként való exportálását. A következő JSON-forgatókönyvek érhetők el:
- Relációs adatok formázása JSON formátumban záradék használatával
FOR JSON
. - JSON-adatok használata
- JSON-adatok lekérdezése JSON skaláris függvényekkel.
- JSON átalakítása táblázatos formátumba függvény használatával
OPENJSON
.
Relációs adatok formázása JSON formátumban
Ha olyan webszolgáltatással rendelkezik, amely adatokat fogad az adatbázisrétegből, és JSON formátumban ad választ, vagy ügyféloldali JavaScript-keretrendszereket vagy kódtárakat, amelyek JSON-ként formázott adatokat fogadnak el, az adatbázis tartalmát közvetlenül JSON-ként formázhatja egy SQL-lekérdezésben. Többé nem kell olyan alkalmazáskódot írnia, amely az Azure SQL Database-ből vagy az Azure SQL Managed Instance-ből származó eredményeket JSON-ként formázza, vagy tartalmaz néhány JSON szerializálási kódtárat a táblázatos lekérdezési eredmények konvertálásához, majd az objektumok JSON-formátumba való szerializálásához. Ehelyett használhatja a FOR JSON záradékot az SQL-lekérdezések eredményeinek JSON-ként való formázására, és közvetlenül az alkalmazásban való használatra.
Az alábbi példában a Sales.Customer
táblázat sorai JSON-ként vannak formázva a FOR JSON záradék használatával:
select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH
A FOR JSON PATH záradék JSON-szövegként formázja a lekérdezés eredményeit. Az oszlopnevek kulcsként használatosak, míg a cellaértékek JSON-értékekként jönnek létre:
[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]
Az eredményhalmaz JSON-tömbként van formázva, ahol minden sor külön JSON-objektumként van formázva.
A PATH azt jelzi, hogy testre szabhatja a JSON-eredmény kimeneti formátumát az oszlop aliasai pont jelölésének használatával. A következő lekérdezés megváltoztatja a "CustomerName" kulcs nevét a kimeneti JSON formátumban, és a telefonszámokat és faxszámokat a "Névjegy" alobjektumba helyezi:
select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
A lekérdezés kimenete a következőképpen néz ki:
{
"Name":"Nada Jovanovic",
"Contact":{
"Phone":"(215) 555-0100",
"Fax":"(215) 555-0101"
}
}
Ebben a példában egyetlen JSON-objektumot adtunk vissza tömb helyett a WITHOUT_ARRAY_WRAPPER beállítás megadásával. Ezt a lehetőséget akkor használhatja, ha tudja, hogy a lekérdezés eredményeként egyetlen objektumot ad vissza.
A FOR JSON záradék fő értéke, hogy lehetővé teszi összetett hierarchikus adatok visszaadását az adatbázisból beágyazott JSON-objektumokként vagy tömbökként formázva. Az alábbi példa bemutatja, hogyan lehet belefoglalni a Orders
táblázat azon sorait, amelyek a Customer
következő beágyazott tömbhöz Orders
tartoznak:
select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
join Sales.Orders Orders
on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
Ahelyett, hogy külön lekérdezéseket küldene az ügyféladatok lekéréséhez, majd a kapcsolódó rendelések listájának lekéréséhez, egyetlen lekérdezéssel lekérheti az összes szükséges adatot, ahogyan az alábbi mintakimenetben látható:
{
"Name":"Nada Jovanovic",
"Phone":"(215) 555-0100",
"Fax":"(215) 555-0101",
"Orders":[
{"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
{"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
{"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
]
}
JSON-adatok használata
Ha nem rendelkezik szigorúan strukturált adatokkal, ha összetett alobjektumokkal, tömbök vagy hierarchikus adatokkal rendelkezik, vagy ha az adatstruktúrák idővel fejlődnek, a JSON formátum segíthet bármilyen összetett adatstruktúra megjelenítésében.
A JSON egy szöveges formátum, amely bármely más sztringtípushoz hasonlóan használható az Azure SQL Database-ben és a felügyelt Azure SQL-példányban. JSON-adatokat normál NVARCHAR-ként küldhet vagy tárolhat:
CREATE TABLE Products (
Id int identity primary key,
Title nvarchar(200),
Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
insert into Products(Title, Data)
values(@title, @json)
END
A példában használt JSON-adatokat az NVARCHAR(MAX) típussal jelölik. A JSON beszúrható ebbe a táblába, vagy a tárolt eljárás argumentumaként adható meg szabványos Transact-SQL szintaxissal, ahogyan az alábbi példában látható:
EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'
Minden ügyféloldali nyelv vagy kódtár, amely sztringadatokkal dolgozik az Azure SQL Database-ben és az Azure SQL Managed Instance-ben, JSON-adatokkal is működni fog. A JSON bármely olyan táblában tárolható, amely támogatja az NVARCHAR típust, például memóriaoptimalizált táblában vagy rendszerverziós táblában. A JSON nem vezet be semmilyen korlátozást sem az ügyféloldali kódban, sem az adatbázisrétegben.
JSON-adatok lekérdezése
Ha az Azure SQL-táblákban JSON-ként formázott adatok vannak tárolva, a JSON-függvényekkel ezeket az adatokat bármely SQL-lekérdezésben használhatja.
Az Azure SQL Database-ben és az Azure SQL Managed Instance-ben elérhető JSON-függvények lehetővé teszik a JSON-ként formázott adatok bármely más SQL-adattípusként való kezelését. A JSON-szövegből egyszerűen kinyerhet értékeket, és JSON-adatokat használhat bármely lekérdezésben:
select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'
update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1
A JSON_VALUE függvény az Adat oszlopban tárolt JSON-szövegből nyer ki egy értéket. Ez a függvény JavaScript-szerű elérési utat használ a kinyerendő JSON-szöveg egy értékére való hivatkozáshoz. A kinyert érték az SQL-lekérdezés bármely részében használható.
A JSON_QUERY függvény hasonló a JSON_VALUE. A JSON_VALUE ellentétben ez a függvény összetett alobjektumokat, például tömböket vagy JSON-szövegbe helyezett objektumokat nyer ki.
A JSON_MODIFY függvénnyel megadhatja a frissíteni kívánt JSON-szövegben lévő érték elérési útját, valamint egy új értéket, amely felülírja a régit. Így egyszerűen frissítheti a JSON-szöveget a teljes struktúra újraelemzése nélkül.
Mivel a JSON szabványos szövegben van tárolva, nincs garancia arra, hogy a szövegoszlopokban tárolt értékek megfelelően vannak formázva. A JSON-oszlopban tárolt szöveg helyes formázását a szabványos Azure SQL Database ellenőrzési kényszerek és az ISJSON függvény használatával ellenőrizheti:
ALTER TABLE Products
ADD CONSTRAINT [Data should be formatted as JSON]
CHECK (ISJSON(Data) > 0)
Ha a bemeneti szöveg megfelelően formázott JSON,az ISJSON függvény az 1 értéket adja vissza. A JSON-oszlop minden beszúrása vagy frissítésekor ez a korlátozás ellenőrzi, hogy az új szöveges érték nem hibás JSON-e.
JSON átalakítása táblázatos formátumba
Az Azure SQL Database és a felügyelt Azure SQL-példány lehetővé teszi a JSON-gyűjtemények táblázatos formátumba való átalakítását, valamint JSON-adatok betöltését vagy lekérdezését.
Az OPENJSON egy tábla-érték függvény, amely JSON-szöveget elemez, JSON-objektumok tömböt keres, végigfuttat a tömb elemein, és egy sort ad vissza a kimeneti eredményben a tömb minden eleméhez.
A fenti példában megadhatja, hogy hol keresse meg a megnyitni kívánt JSON-tömböt (a $-ban). Rendelések elérési útja), az eredményként visszaadandó oszlopok, valamint a cellaként visszaadott JSON-értékek megkeresésének helye.
A változóban lévő @orders JSON-tömböket sorhalmazsá alakíthatjuk, elemezhetjük ezt az eredményhalmazt, vagy beszúrhatunk sorokat egy standard táblába:
CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN
insert into Orders(Number, Date, Customer, Quantity)
select Number, Date, Customer, Quantity
FROM OPENJSON (@orders)
WITH (
Number varchar(200),
Date datetime,
Customer varchar(200),
Quantity int
)
END
A JSON-tömbként formázott és a tárolt eljárás paramétereként megadott rendelések gyűjteménye elemezhető és beilleszthető az Orders táblába.