Megosztás a következőn keresztül:

Ismerkedés a JSON-funkciókkal

A következőkre vonatkozik:Azure SQL DatabaseAzure SQL Managed InstanceSQL-adatbázis a Fabricben

Az Azure SQL Database, a Microsoft Fabric 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

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. Nem kell többé olyan alkalmazáskódot írnia, amely JSON-ként formázza az eredményeket, 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 záradékot az FOR JSON 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.

PATH azt jelzi, hogy testre szabhatja a JSON-eredmény kimeneti formátumát pont jelöléssel az oszlop aliasaiban. Az alábbi lekérdezés megváltoztatja a CustomerName kulcs nevét a kimeneti JSON formátumban, és a telefonszámokat és faxszámokat az Contact 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(z) FOR JSON záradék fő értéke, hogy lehetővé teszi az összetett, hierarchikus adatok visszaadását az adatbázisból oly módon, hogy azok beágyazott JSON-objektumokként vagy tömbökként legyenek 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 adatok lekéréséhez Customer , majd egy kapcsolódó Orderslista 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ó. JSON-adatokat küldhet vagy tárolhat standard nvarcharként:

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, 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 a táblákban JSON-ként formázott adatok vannak tárolva, a JSON-függvények segítségével ezeket az adatokat bármely SQL-lekérdezésben használhatja.

A 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 következőhöz JSON_VALUE: . Ezzel ellentétben JSON_VALUEez 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ény lehetővé teszi az érték elérési útját a frissíteni kívánt JSON-szövegben, 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 ellenőrzési korlátozások és a 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-t tartalmaz, a ISJSON függvény az értéket 1adja 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

A JSON-gyűjteményeket táblázatos formátumba alakíthatja át, és JSON-adatokat tölthet be vagy kérdezhet le.

OPENJSON egy táblaértékű T-SQL-függvény, amely JSON-szöveget elemez, JSON-objektumok tömböt keres, végigvezeti a tömb elemein, és egy sort ad vissza a kimeneti eredményben a tömb minden eleméhez.

Képernyőképek és kódrészletek a JSON-minta táblázatos adatairól.

A példában megadhatja, hogy hol keresse meg a megnyitni kívánt JSON-tömböt (az $.Orders elérési úton), milyen oszlopokat kell visszaadni, és hol találhatók a cellákként visszaadott JSON-értékek.

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.

Kapcsolódó tartalom