Komma igång med JSON-funktioner i Azure SQL Database och Azure SQL Managed Instance

Artikel
07/31/2023

Gäller för:Azure SQL Database Azure SQL Managed Instance

Med Azure SQL Database och Azure SQL Managed Instance kan du parsa och fråga efter data som representeras i JSON-format (JavaScript Object Notation ) och exportera dina relationsdata som JSON-text. Följande JSON-scenarier är tillgängliga:

Formatera relationsdata i JSON-format med hjälp av -sats FOR JSON .
Arbeta med JSON-data
Köra frågor mot JSON-data med hjälp av JSON-skalärfunktioner.
Omvandla JSON till tabellformat med hjälp av OPENJSON funktionen.

Formatera relationsdata i JSON-format

Om du har en webbtjänst som tar data från databaslagret och ger ett svar i JSON-format, eller JavaScript-ramverk eller -bibliotek på klientsidan som accepterar data som är formaterade som JSON, kan du formatera databasinnehållet som JSON direkt i en SQL-fråga. Du behöver inte längre skriva programkod som formaterar resultat från Azure SQL Database eller Azure SQL Managed Instance som JSON, eller inkludera något JSON-serialiseringsbibliotek för att konvertera tabellfrågeresultat och sedan serialisera objekt till JSON-format. I stället kan du använda FOR JSON-satsen för att formatera SQL-frågeresultat som JSON och använda den direkt i ditt program.

I följande exempel formateras rader från Sales.Customer tabellen som JSON med hjälp av FOR JSON-satsen:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

FOR JSON PATH-satsen formaterar resultatet av frågan som JSON-text. Kolumnnamn används som nycklar, medan cellvärdena genereras som JSON-värden:

[
{"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"}
]

Resultatuppsättningen formateras som en JSON-matris där varje rad formateras som ett separat JSON-objekt.

PATH anger att du kan anpassa utdataformatet för ditt JSON-resultat med hjälp av punkt notation i kolumnalias. Följande fråga ändrar namnet på "CustomerName"-nyckeln i JSON-utdataformatet och placerar telefonnummer och faxnummer i underobjektet "Kontakt":

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Utdata för den här frågan ser ut så här:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

I det här exemplet returnerade vi ett enda JSON-objekt i stället för en matris genom att ange alternativet WITHOUT_ARRAY_WRAPPER . Du kan använda det här alternativet om du vet att du returnerar ett enda objekt som ett resultat av frågan.

Huvudvärdet för FOR JSON-satsen är att du kan returnera komplexa hierarkiska data från databasen som är formaterad som kapslade JSON-objekt eller matriser. I följande exempel visas hur du tar med raderna från tabellen Orders som tillhör Customer som en kapslad matris med Orders:

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

I stället för att skicka separata frågor för att hämta kunddata och sedan hämta en lista över relaterade beställningar kan du hämta alla nödvändiga data med en enda fråga, som du ser i följande exempelutdata:

{
  "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"}
  ]
}

Arbeta med JSON-data

Om du inte har strikt strukturerade data, om du har komplexa underobjekt, matriser eller hierarkiska data, eller om dina datastrukturer utvecklas över tid, kan JSON-formatet hjälpa dig att representera alla komplexa datastrukturer.

JSON är ett textformat som kan användas som andra strängtyper i Azure SQL Database och Azure SQL Managed Instance. Du kan skicka eller lagra JSON-data som standard NVARCHAR:

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

JSON-data som används i det här exemplet representeras med hjälp av typen NVARCHAR(MAX). JSON kan infogas i den här tabellen eller anges som ett argument för den lagrade proceduren med standardsyntaxen transact-SQL enligt följande exempel:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

Alla språk eller bibliotek på klientsidan som fungerar med strängdata i Azure SQL Database och Azure SQL Managed Instance fungerar också med JSON-data. JSON kan lagras i valfri tabell som stöder NVARCHAR-typen, till exempel en minnesoptimerad tabell eller en systemversionstabell. JSON introducerar inte några begränsningar i koden på klientsidan eller i databaslagret.

Köra frågor mot JSON-data

Om du har data som är formaterade som JSON som lagras i Azure SQL-tabeller kan du med JSON-funktioner använda dessa data i valfri SQL-fråga.

Med JSON-funktioner som är tillgängliga i Azure SQL Database och Azure SQL Managed Instance kan du behandla data som är formaterade som JSON som andra SQL-datatyper. Du kan enkelt extrahera värden från JSON-texten och använda JSON-data i valfri fråga:

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

Funktionen JSON_VALUE extraherar ett värde från JSON-text som lagras i kolumnen Data. Den här funktionen använder en JavaScript-liknande sökväg för att referera till ett värde i JSON-text som ska extraheras. Det extraherade värdet kan användas i valfri del av SQL-frågan.

Funktionen JSON_QUERY liknar JSON_VALUE. Till skillnad från JSON_VALUE extraherar den här funktionen komplexa underobjekt, till exempel matriser eller objekt som placeras i JSON-text.

Med funktionen JSON_MODIFY kan du ange sökvägen till värdet i JSON-texten som ska uppdateras, samt ett nytt värde som skriver över det gamla. På så sätt kan du enkelt uppdatera JSON-text utan att ändra hela strukturen.

Eftersom JSON lagras i en standardtext finns det inga garantier för att de värden som lagras i textkolumner är korrekt formaterade. Du kan kontrollera att text som lagras i JSON-kolumnen är korrekt formaterad med hjälp av standardbegränsningar för Azure SQL Database-kontroller och ISJSON-funktionen:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Om indatatexten är korrekt formaterad med JSON returnerar funktionen ISJSON värdet 1. Vid varje infogning eller uppdatering av JSON-kolumnen kontrollerar den här begränsningen att det nya textvärdet inte är felaktigt JSON.

Omvandla JSON till tabellformat

Med Azure SQL Database och Azure SQL Managed Instance kan du även omvandla JSON-samlingar till tabellformat och läsa in eller fråga efter JSON-data.

OPENJSON är en tabellvärdefunktion som parsar JSON-text, letar upp en matris med JSON-objekt, itererar genom elementen i matrisen och returnerar en rad i utdataresultatet för varje element i matrisen.

JSON tabular

I exemplet ovan kan vi ange var du ska hitta den JSON-matris som ska öppnas (i $. Ordrar sökväg), vilka kolumner som ska returneras som resultat och var du hittar de JSON-värden som ska returneras som celler.

Vi kan omvandla en JSON-matris i variabeln @orders till en uppsättning rader, analysera den här resultatuppsättningen eller infoga rader i en standardtabell:

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

Samlingen med beställningar som är formaterade som en JSON-matris och tillhandahålls som en parameter till den lagrade proceduren kan parsas och infogas i tabellen Beställningar.