Dela via

Verifiera, fråga och ändra JSON-data med inbyggda funktioner

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Det inbyggda stödet för JSON i SQL Database Engine innehåller följande funktioner:

  • ISJSON testar om en sträng innehåller giltig JSON.
  • JSON_VALUE extraherar ett skalärt värde från en JSON-sträng.
  • JSON_QUERY extraherar ett objekt eller en matris från en JSON-sträng.
  • JSON_MODIFY uppdaterar värdet för en egenskap i en JSON-sträng och returnerar den uppdaterade JSON-strängen.

För alla JSON-funktioner läser du JSON-funktioner (Transact-SQL).

Kodexemplen i den här artikeln använder AdventureWorks2025- eller AdventureWorksDW2025-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

JSON-text för exemplen på den här sidan

Exemplen på den här sidan använder JSON-texten som liknar innehållet som visas i följande exempel:

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

Det här JSON-dokumentet, som innehåller kapslade komplexa element, lagras i följande exempeltabell:

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

JSON-funktioner fungerar på samma sätt oavsett om JSON-dokumentet lagras i varchar, nvarchar eller den interna json-datatypen .

Verifiera JSON-text med hjälp av funktionen ISJSON

Funktionen ISJSON testar om en sträng innehåller giltig JSON.

I följande exempel returneras rader där JSON-kolumnen innehåller giltig JSON-text. Utan explicit JSON-villkor kan du ange valfri text i kolumnen nvarchar :

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

Mer information finns i ISJSON (Transact-SQL).

Extrahera ett värde från JSON-text med hjälp av funktionen JSON_VALUE

Funktionen JSON_VALUE extraherar ett skalärt värde från en JSON-sträng. Följande fråga returnerar de dokument där id JSON-fältet matchar värdet DesaiFamily, sorterat efter city och state JSON-fält:

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

Resultatet av den här frågan visas i följande tabell:

Name City County
DesaiFamily NY Manhattan

Mer information finns i JSON_VALUE.

Extrahera ett objekt eller en matris från JSON-text med hjälp av funktionen JSON_QUERY

Funktionen JSON_QUERY extraherar ett objekt eller en matris från en JSON-sträng. I följande exempel visas hur du returnerar ett JSON-fragment i frågeresultat.

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';

Resultatet av den här frågan visas i följande tabell:

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

Mer information finns i JSON_QUERY.

Parsa kapslade JSON-samlingar

** OPENJSON funktionen gör att du kan omvandla JSON-delmatrisen till en raduppsättning och sedan koppla den till överordnade elementet. Du kan till exempel returnera alla familjedokument och "koppla" dem till deras children objekt som lagras som en inre JSON-matris:

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

Resultatet av den här frågan visas i följande tabell:

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

Två rader returneras eftersom en huvudrad är knuten till två barnrader som genereras genom att två element i barnsubarrayen parsas. OPENJSON funktionen parsar children fragment från doc kolumnen och returnerar grade och givenName från varje element som en uppsättning rader. Den här raduppsättningen kan kopplas till det överordnade dokumentet.

Sök kapslade hierarkiska JSON-underarrayer

Du kan använda flera CROSS APPLY OPENJSON anrop för att köra frågor mot kapslade JSON-strukturer. JSON-dokumentet som används i det här exemplet har en kapslad array med namnet children, där varje element har en kapslad array av pets. Följande fråga parsar barn från varje dokument, returnerar varje arrayobjekt som rad och sedan parsar pets arrayen.

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;

Det första OPENJSON anropet returnerar ett fragment av en children-matris med hjälp av AS JSON-klausulen. Det här matrisfragmentet tillhandahålls till den andra OPENJSON-funktionen som returnerar givenName och firstName för varje barn, samt matrisen av pets. Arrayen pets tillhandahålls till den tredje OPENJSON funktionen som returnerar givenName för husdjuret.

Resultatet av den här frågan visas i följande tabell:

familyName barnFörnamn husdjursnamn
Desai Jesse Goofy
Desai Jesse Shadow
Desai Lisa NULL

Rotdokumentet är kopplat till två children rader som returneras av det första OPENJSON(children)-anropet, vilket skapar två rader (eller tupplar). Sedan kopplas varje rad till de nya raderna som genereras med hjälp av OPENJSON(pets) operatorn OUTER APPLY . Jesse har två husdjur, så (Desai, Jesse) är ansluten med två rader som genereras för Goofy och Shadow. Lisa har inte husdjuren, så det finns inga rader som returneras av OPENJSON(pets) för den här tuppeln. Men eftersom vi använder OUTER APPLY, får vi NULL i kolumnen. Om vi lägger CROSS APPLY i stället för OUTER APPLYskulle Lisa inte returneras i resultatet eftersom det inte finns några husdjursrader som kan kopplas till den här tuppeln.

Jämför JSON_VALUE och JSON_QUERY

Den viktigaste skillnaden mellan JSON_VALUE och JSON_QUERY är att JSON_VALUE returnerar ett skalärt värde, medan JSON_QUERY returnerar ett objekt eller en matris.

Överväg följande JSON-exempeltext.

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

I den här JSON-exempeltexten är datamedlemmar "a" och "c" strängvärden, medan datamedlemmen "b" är en matris. JSON_VALUE och JSON_QUERY returnera följande resultat:

Path JSON_VALUE returnerar JSON_QUERY returnerar
$ NULL eller fel { "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a [1,2] NULL eller fel
$.b NULL eller fel [1,2]
$.b[0] 1 NULL eller fel
$.c hi NULL eller fel

Testa JSON_VALUE och JSON_QUERY med AdventureWorks-exempeldatabasen

Testa de inbyggda funktionerna som beskrivs i den här artikeln genom att köra följande exempel med exempeldatabasen AdventureWorks2025 . Mer information om hur du lägger till JSON-data för testning genom att köra ett skript finns i Inbyggt JSON-stöd för testkörning.

I följande exempel Info innehåller kolumnen i SalesOrder_json tabellen JSON-text.

Exempel 1 – Returnera både standardkolumner och JSON-data

Följande fråga returnerar värden från både standardrelationskolumner och från en JSON-kolumn.

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;

Exempel 2 – Aggregera och filtrera JSON-värden

Följande fråga aggregerar delsummor efter kundnamn (lagras i JSON) och status (lagras i en vanlig kolumn). Sedan filtreras resultatet efter stad (lagras i JSON) och OrderDate (lagras i en vanlig kolumn).

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;

Uppdatera egenskapsvärden i JSON-text med hjälp av funktionen JSON_MODIFY

Funktionen JSON_MODIFY uppdaterar värdet för en egenskap i en JSON-sträng och returnerar den uppdaterade JSON-strängen.

I följande exempel uppdateras värdet för en JSON-egenskap i en variabel som innehåller JSON.

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

Mer information finns i JSON_MODIFY.

Relaterat innehåll

  • Last updated on