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 Orderstartoznak:

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.