Delen via

JSON-gegevens valideren, opvragen en wijzigen met ingebouwde functies

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

De ingebouwde ondersteuning voor JSON in de SQL Database Engine bevat de volgende functies:

  • ISJSON test of een tekenreeks geldige JSON bevat.
  • JSON_VALUE extraheert een scalaire waarde uit een JSON-tekenreeks.
  • JSON_QUERY extraheert een object of matrix uit een JSON-tekenreeks.
  • JSON_MODIFY werkt de waarde van een eigenschap in een JSON-tekenreeks bij en retourneert de bijgewerkte JSON-tekenreeks.

Raadpleeg JSON-functies (Transact-SQL) voor alle JSON-functies.

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2025 of AdventureWorksDW2025 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

JSON-tekst voor de voorbeelden op deze pagina

In de voorbeelden op deze pagina wordt de JSON-tekst gebruikt die vergelijkbaar is met de inhoud die in het volgende voorbeeld wordt weergegeven:

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    },
    "creationDate": 1431620462,
    "isRegistered": false
}

Dit JSON-document, dat geneste complexe elementen bevat, wordt opgeslagen in de volgende voorbeeldtabel:

CREATE TABLE Families (
    id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
    [doc] NVARCHAR(MAX)
);

JSON-functies werken hetzelfde, ongeacht of het JSON-document is opgeslagen in varchar, nvarchar of het systeemeigen json-gegevenstype .

JSON-tekst valideren met behulp van de functie ISJSON

De ISJSON functie test of een tekenreeks geldige JSON bevat.

In het volgende voorbeeld worden rijen geretourneerd waarin de JSON-kolom geldige JSON-tekst bevat. Zonder expliciete JSON-beperking kunt u tekst invoeren in de kolom nvarchar :

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

Zie ISJSON (Transact-SQL) voor meer informatie.

Een waarde extraheren uit JSON-tekst met behulp van de functie JSON_VALUE

De JSON_VALUE functie extraheert een scalaire waarde uit een JSON-tekenreeks. De volgende query retourneert de documenten waarin het id JSON-veld overeenkomt met de waarde DesaiFamily, gesorteerd op city en state JSON-velden:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

De resultaten van deze query worden weergegeven in de volgende tabel:

Name City County
DesaiFamily NY Manhattan

Zie JSON_VALUE voor meer informatie.

Een object of matrix extraheren uit JSON-tekst met behulp van de functie JSON_QUERY

Met de JSON_QUERY functie wordt een object of een matrix uit een JSON-tekenreeks geëxtraheerd. In het volgende voorbeeld ziet u hoe u een JSON-fragment retourneert in queryresultaten.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
    JSON_QUERY(f.doc, '$.parents') AS Parents,
    JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';

De resultaten van deze query worden weergegeven in de volgende tabel:

Address Parents Ouder
{ "state": "NY", "county": "Manhattan", "city": "NY" } [ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ] { "familyName": "Desai", "givenName": "Prashanth" }

Zie JSON_QUERY voor meer informatie.

Geneste JSON-verzamelingen parseren

OPENJSON met de functie kunt u een JSON-subarray omzetten in de rijenreeks en deze vervolgens samenvoegen met het bovenliggende element. U kunt bijvoorbeeld alle gezinsdocumenten retourneren en ze 'samenvoegen' met hun children objecten die zijn opgeslagen als een interne JSON-matrix:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    c.givenName,
    c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
    grade INT,
    givenName NVARCHAR(100)
) c

De resultaten van deze query worden weergegeven in de volgende tabel:

Name City givenName grade
DesaiFamily NY Jesse 1
DesaiFamily NY Lisa 8

Er worden twee rijen geretourneerd, omdat één bovenliggende rij wordt samengevoegd met twee kinder-rijen, geproduceerd door het parseren van twee elementen uit de subarray van kinderen. OPENJSONfunctie parseert fragment uit de children kolom en retourneert docgrade en givenName van elk element als een set rijen. Deze rijenset kan worden samengevoegd met het ouderdocument.

Geneste hiërarchische JSON-subarrays opvragen

U kunt meerdere CROSS APPLY OPENJSON aanroepen toepassen om een query uit te voeren op geneste JSON-structuren. Het JSON-document dat in dit voorbeeld wordt gebruikt, heeft een geneste array genaamd children, waarbij elk kind een geneste array pets heeft. De volgende query parseert kindobjecten uit elk document, retourneert elk array-object als rij en parseert vervolgens de pets array.

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

De eerste OPENJSON aanroep retourneert een fragment van children matrix met behulp van de AS JSON-clausule. Dit arrayfragment wordt geleverd aan de tweede OPENJSON functie die givenName en firstName van elk kind retourneert, evenals de array van pets. De array van pets wordt aan de derde OPENJSON-functie verstrekt die de givenName van het huisdier retourneert.

De resultaten van deze query worden weergegeven in de volgende tabel:

familyName kindVoornaam huisdierNaam
Desai Jesse Goofy
Desai Jesse Shadow
Desai Lisa NULL

Het hoofddocument wordt samengevoegd met twee children rijen die worden geretourneerd door de eerste OPENJSON(children)-aanroep, waardoor twee rijen (of tuples) ontstaan. Vervolgens wordt elke rij samengevoegd met de nieuwe rijen die worden gegenereerd door OPENJSON(pets) met behulp van de OUTER APPLY-operator. Jesse heeft twee huisdieren, dus (Desai, Jesse) wordt samengevoegd met twee rijen die zijn gegenereerd voor Goofy en Shadow. Lisa heeft geen huisdieren, dus er zijn geen rijen geretourneerd door OPENJSON(pets) voor deze tuple. Echter, omdat we OUTER APPLY gebruiken, krijgen we NULL in de kolom. Als we CROSS APPLY in plaats van OUTER APPLY gebruiken, zou Lisa niet worden opgenomen in het resultaat omdat er geen rijen van huisdieren zijn die aan deze tuple kunnen worden gekoppeld.

JSON_VALUE en JSON_QUERY vergelijken

Het belangrijkste verschil tussen JSON_VALUE en JSON_QUERY is dat JSON_VALUE een scalaire waarde retourneert, terwijl JSON_QUERY een object of een matrix wordt geretourneerd.

Bekijk de volgende JSON-voorbeeldtekst.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

In deze JSON-voorbeeldtekst zijn gegevensleden 'a' en 'c' tekenreekswaarden, terwijl gegevenslid 'b' een matrix is. JSON_VALUE en JSON_QUERY geven de volgende resultaten terug:

Path JSON_VALUE retourneert JSON_QUERY retourneert
$ NULL of fout { "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a [1,2] NULL of fout
$.b NULL of fout [1,2]
$.b[0] 1 NULL of fout
$.c hi NULL of fout

Test JSON_VALUE en JSON_QUERY met de AdventureWorks-voorbeelddatabase

Test de ingebouwde functies die in dit artikel worden beschreven door de volgende voorbeelden uit te voeren met de AdventureWorks2025 voorbeelddatabase. Zie De ingebouwde JSON-ondersteuning voor teststations voor meer informatie over het toevoegen van JSON-gegevens voor testen door een script uit te voeren.

In de volgende voorbeelden bevat de Info kolom in de SalesOrder_json tabel JSON-tekst.

Voorbeeld 1: zowel standaardkolommen als JSON-gegevens retourneren

De volgende query retourneert waarden uit zowel standaard relationele kolommen als uit een JSON-kolom.

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    AccountNumber,
    TotalDue,
    JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
    JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
    JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
    JSON_VALUE(Info, '$.ShippingInfo.City') City,
    JSON_VALUE(Info, '$.Customer.Name') Customer,
    JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;

Voorbeeld 2: JSON-waarden aggregeren en filteren

Met de volgende query worden subtotalen samengevoegd op klantnaam (opgeslagen in JSON) en status (opgeslagen in een gewone kolom). Vervolgens worden de resultaten gefilterd op plaats (opgeslagen in JSON) en OrderDate (opgeslagen in een gewone kolom).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid = 3;
SET @city = N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
    Status,
    SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
    AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
    AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
    Status
HAVING SUM(SubTotal) > 1000;

Eigenschapswaarden in JSON-tekst bijwerken met behulp van de functie JSON_MODIFY

De JSON_MODIFY functie werkt de waarde van een eigenschap in een JSON-tekenreeks bij en retourneert de bijgewerkte JSON-tekenreeks.

In het volgende voorbeeld wordt de waarde van een JSON-eigenschap bijgewerkt in een variabele die JSON bevat.

SET @info = JSON_MODIFY(@jsonInfo, '$.info.address[0].town', 'London');

Zie JSON_MODIFY voor meer informatie.

Verwante inhoud

  • Last updated on