Aan de slag met JSON-functies in Azure SQL Database en Azure SQL Managed Instance

  • Artikel

Van toepassing op: Azure SQL DatabaseAzure SQL Managed Instance

Met Azure SQL Database en Azure SQL Managed Instance kunt u gegevens parseren en er query's op uitvoeren die worden weergegeven in de 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 waarmee resultaten van Azure SQL Database of Azure SQL Managed Instance als JSON worden opgemaakt, of een aantal JSON-serialisatiebibliotheek opnemen 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 als JSON op te maken 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 de COMPONENT FOR JSON PATH 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 sleutel CustomerName in de JSON-uitvoerindeling gewijzigd en worden telefoonnummers en faxnummers in het subobject Contact 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 klantgegevens op te halen en vervolgens een lijst met gerelateerde orders 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 in Azure SQL Database en Azure SQL Managed Instance. U kunt JSON-gegevens verzenden of opslaan als een 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 NVARCHAR(MAX)-type 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 werkt met tekenreeksgegevens in Azure SQL Database en Azure SQL Managed Instance, 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 Azure SQL-tabellen, kunt u met JSON-functies deze gegevens gebruiken in een SQL-query.

Met JSON-functies die beschikbaar zijn in Azure SQL Database en Azure SQL Managed Instance, 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 de functie JSON_VALUE 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 functie JSON_QUERY is vergelijkbaar met JSON_VALUE. In tegenstelling tot JSON_VALUE extraheert deze functie complexe subobjecten, zoals matrices of objecten die in JSON-tekst worden geplaatst.

Met de functie JSON_MODIFY 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 standaardbeperkingen voor Azure SQL Database-controles en de FUNCTIE ISJSON:

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

Als de invoertekst JSON juist is opgemaakt, retourneert de FUNCTIE ISJSON 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

Met Azure SQL Database en Azure SQL Managed Instance kunt u JSON-verzamelingen ook transformeren in tabelvorm en JSON-gegevens laden of er query's op uitvoeren.

OPENJSON is een functie voor tabelwaarde waarmee JSON-tekst wordt geparseerd, een matrix met JSON-objecten wordt gevonden, de elementen van de matrix worden herhaald en één rij in het uitvoerresultaat voor elk element van de matrix wordt geretourneerd.

JSON tabular

In het bovenstaande voorbeeld kunnen we opgeven waar de JSON-matrix moet worden geopend (in de $. Het pad Orders), welke kolommen als resultaat moeten worden geretourneerd en waar u de JSON-waarden kunt vinden 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.