Delen via

Aan de slag met JSON-functies

van toepassing op:Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Fabric

Met Azure SQL Database, SQL Database in Microsoft Fabric en Azure SQL Managed Instance kunt u gegevens parseren en er query's op uitvoeren die worden weergegeven in JSON-indeling ( JavaScript Object Notation) en uw relationele gegevens exporteren als JSON-tekst. De volgende JSON-scenario's zijn beschikbaar:

Relationele gegevens opmaken in JSON-indeling

Als u een webservice hebt die gegevens uit de databaselaag haalt en een antwoord geeft in JSON-indeling of JavaScript-frameworks of bibliotheken aan de clientzijde die gegevens accepteren die zijn opgemaakt als JSON, kunt u uw database-inhoud rechtstreeks in een SQL-query opmaken als JSON. U hoeft geen toepassingscode meer te schrijven die resultaten opmaakt als JSON of een JSON-serialisatiebibliotheek bevat om queryresultaten in tabelvorm te converteren en objecten vervolgens te serialiseren naar JSON-indeling. In plaats daarvan kunt u de FOR JSON component gebruiken om SQL-queryresultaten op te maken als JSON en deze rechtstreeks in uw toepassing te gebruiken.

In het volgende voorbeeld worden rijen uit de Sales.Customer tabel opgemaakt als JSON met behulp van de FOR JSON-component:

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

Met FOR JSON PATH de component worden de resultaten van de query opgemaakt als JSON-tekst. Kolomnamen worden gebruikt als sleutels, terwijl de celwaarden worden gegenereerd als JSON-waarden:

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

De resultatenset wordt opgemaakt als een JSON-matrix waarin elke rij wordt opgemaakt als een afzonderlijk JSON-object.

PATH geeft aan dat u de uitvoerindeling van uw JSON-resultaat kunt aanpassen met behulp van puntnotatie in kolomaliassen. Met de volgende query wordt de naam van de CustomerName sleutel in de JSON-uitvoerindeling gewijzigd en worden telefoonnummers en faxnummers in het Contact subobject geplaatst:

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

De uitvoer van deze query ziet er als volgt uit:

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

In dit voorbeeld hebben we één JSON-object geretourneerd in plaats van een matrix door de optie WITHOUT_ARRAY_WRAPPER op te geven. U kunt deze optie gebruiken als u weet dat u één object retourneert als gevolg van een query.

De belangrijkste waarde van de FOR JSON component is dat u hiermee complexe hiërarchische gegevens kunt retourneren uit uw database die zijn opgemaakt als geneste JSON-objecten of matrices. In het volgende voorbeeld ziet u hoe u de rijen uit de tabel opneemt die deel uitmaken van de OrdersCustomer tabel als een geneste matrix van 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

In plaats van afzonderlijke query's te verzenden om gegevens op te halen Customer en vervolgens een lijst met gerelateerde Ordersgegevens op te halen, kunt u alle benodigde gegevens met één query ophalen, zoals wordt weergegeven in de volgende voorbeelduitvoer:

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

Werken met JSON-gegevens

Als u geen strikt gestructureerde gegevens hebt, als u complexe subobjecten, matrices of hiërarchische gegevens hebt, of als uw gegevensstructuren zich in de loop van de tijd ontwikkelen, kan de JSON-indeling u helpen bij het vertegenwoordigen van elke complexe gegevensstructuur.

JSON is een tekstindeling die kan worden gebruikt als elk ander tekenreekstype. U kunt JSON-gegevens verzenden of opslaan als standaard 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

De JSON-gegevens die in dit voorbeeld worden gebruikt, worden vertegenwoordigd door het type nvarchar(MAX) te gebruiken. JSON kan worden ingevoegd in deze tabel of worden opgegeven als argument van de opgeslagen procedure met behulp van de standaard-Transact-SQL syntaxis, zoals wordt weergegeven in het volgende voorbeeld:

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

Elke taal of bibliotheek aan de clientzijde die met tekenreeksgegevens werkt, werkt ook met JSON-gegevens. JSON kan worden opgeslagen in elke tabel die ondersteuning biedt voor het nvarchar-type , zoals een tabel die is geoptimaliseerd voor geheugen of een tabel met systeemversies. JSON introduceert geen beperking in de code aan de clientzijde of in de databaselaag.

Query's uitvoeren op JSON-gegevens

Als u gegevens hebt die zijn opgemaakt als JSON die is opgeslagen in tabellen, kunt u met JSON-functies deze gegevens gebruiken in een SQL-query.

Met JSON-functies kunt u gegevens behandelen die zijn opgemaakt als JSON als elk ander SQL-gegevenstype. U kunt eenvoudig waarden extraheren uit de JSON-tekst en JSON-gegevens gebruiken in elke query:

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

Met JSON_VALUE de functie wordt een waarde geëxtraheerd uit JSON-tekst die is opgeslagen in de kolom Gegevens. Deze functie maakt gebruik van een JavaScript-achtig pad om te verwijzen naar een waarde in JSON-tekst om te extraheren. De geëxtraheerde waarde kan worden gebruikt in elk deel van de SQL-query.

De JSON_QUERY functie is vergelijkbaar met JSON_VALUE. In tegenstelling tot JSON_VALUEdeze functie extraheert deze functie complexe subobjecten, zoals matrices of objecten die in JSON-tekst zijn geplaatst.

Met JSON_MODIFY de functie kunt u het pad opgeven van de waarde in de JSON-tekst die moet worden bijgewerkt, evenals een nieuwe waarde die de oude waarde overschrijft. Op deze manier kunt u eenvoudig JSON-tekst bijwerken zonder de hele structuur te herstellen.

Omdat JSON wordt opgeslagen in een standaardtekst, zijn er geen garanties dat de waarden die zijn opgeslagen in tekstkolommen correct zijn opgemaakt. U kunt controleren of tekst die is opgeslagen in de JSON-kolom correct is opgemaakt met behulp van standaardcontrolebeperkingen en de ISJSON functie:

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

Als de invoertekst JSON correct is opgemaakt, retourneert de ISJSON functie de waarde 1. Bij elke invoeg- of update van de JSON-kolom wordt met deze beperking gecontroleerd of de nieuwe tekstwaarde geen ongeldige JSON is.

JSON transformeren in tabelvorm

U kunt JSON-verzamelingen transformeren in tabelvorm en JSON-gegevens laden of er query's op uitvoeren.

OPENJSON is een T-SQL-functie met tabelwaarde die JSON-tekst parseert, een matrix met JSON-objecten zoekt, doorloopt de elementen van de matrix en retourneert één rij in het uitvoerresultaat voor elk element van de matrix.

Schermopnamen en codefragmenten van voorbeeldgegevens in tabelvorm in JSON.

In het voorbeeld kunnen we opgeven waar de JSON-matrix moet worden geopend (in het $.Orders pad), welke kolommen moeten worden geretourneerd als resultaat en waar de JSON-waarden moeten worden gevonden die als cellen worden geretourneerd.

We kunnen een JSON-matrix in de @orders variabele omzetten in een set rijen, deze resultatenset analyseren of rijen invoegen in een standaardtabel:

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

De verzameling orders die zijn opgemaakt als een JSON-matrix en als parameter voor de opgeslagen procedure kan worden geparseerd en ingevoegd in de tabel Orders.

Verwante inhoud

  • Last updated on