Behandle JSON-data med innebygde funksjoner

12 minutter

Tenk deg et scenario der netthandelsapplikasjonen din lagrer kundepreferanser og bestillingsmetadata som JSON-dokumenter. En mobilapp sender handlekurvdata i JSON-format, og rapporteringssystemet ditt må eksportere produktkataloger som JSON for et web-API. Å jobbe direkte med JSON i databasen din eliminerer behovet for transformasjoner på applikasjonslaget og holder databehandlingen effektiv.

SQL Server, Azure SQL og SQL-databaser i Fabric tilbyr innebygd JSON-støtte som lar deg parse, spørre, opprette og transformere JSON-data direkte i T-SQL. I denne enheten lærer du hvordan du bruker JSON-funksjoner til å hente ut verdier, konstruere JSON-utdata, aggregere data til JSON-arrays og validere JSON-innhold.

Trekk ut verdier med JSON_VALUE og JSON_QUERY

Når du jobber med JSON lagret i databasen din, må du hente ut spesifikke verdier for filtrering, sammenkobling eller visning. SQL Server tilbyr to funksjoner for dette formålet:

JSON_VALUE() trekker ut en skalarverdi (streng, tall, boolsk) fra en JSON-streng:

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd",
        "active": true
    },
    "orderTotal": 1599.99
}';

SELECT 
    JSON_VALUE(@json, '$.customer.id') AS CustomerID,
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;

Resultatsettet vil bli:

CustomerID   CustomerName   OrderTotal
----------   ------------   ----------
12345        Contoso Ltd    1599.99

Funksjonen navigerer JSON-strukturen ved hjelp av stiuttrykket og returnerer verdien som en NVARCHAR(4000) streng. Du kan kaste resultatet til andre datatyper etter behov for beregninger eller sammenligninger.

JSON_QUERY() ekstraherer et JSON-objekt eller array (ikke-skalare verdier):

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd"
    },
    "items": [
        {"product": "Widget", "qty": 5},
        {"product": "Gadget", "qty": 3}
    ]
}';

SELECT 
    JSON_QUERY(@json, '$.customer') AS CustomerObject,
    JSON_QUERY(@json, '$.items') AS ItemsArray;

Resultatsettet vil bli:

CustomerObject                          ItemsArray
--------------------------------------  ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"}     [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]

I motsetning til JSON_VALUE(), JSON_QUERY() bevarer JSON-strukturen, og returnerer objekter og matriser som gyldige JSON-strenger som du kan lagre, sende til andre funksjoner, eller returnere til applikasjoner.

Sti-uttrykket brukes $ til å representere rotelementet, med prikk-notasjon for nestede egenskaper og klamme-notasjon for array-elementer, som i følgende eksempel:

-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;

Resultatet vil bli:

FirstProduct
------------
Widget

Array-indekser starter på 0, så $.items[0] det refererer til det første elementet. Bruk denne syntaksen til å hente ut spesifikke elementer når du kjenner posisjonen, eller kombiner med OPENJSON når du må behandle alle array-elementene.

Tips

Bruk JSON_VALUE() når du trenger en skalar verdi for sammenligninger eller beregninger. Bruk JSON_QUERY() når du trenger å bevare JSON-strukturen til nestede objekter eller matriser.

Parse JSON-arrays med `OPENJSON`

OPENJSON er en tabellverdi-funksjon som transformerer JSON-data til et relasjonelt radsett. Bruk denne funksjonen til å koble JSON-data til relasjonstabeller eller prosessarrayelementer individuelt.

Følgende spørring parser et JSON-array til rader med standardskjema:

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "Widget", "price": 29.99},
    {"id": 2, "name": "Gadget", "price": 49.99},
    {"id": 3, "name": "Gizmo", "price": 19.99}
]';

SELECT * FROM OPENJSON(@json);

Resultatsettet vil bli:

key   value                                          type
---   --------------------------------------------   ----
0     {"id": 1, "name": "Widget", "price": 29.99}   5
1     {"id": 2, "name": "Gadget", "price": 49.99}   5
2     {"id": 3, "name": "Gizmo", "price": 19.99}    5

Uten skjema OPENJSON returnerer tre kolonner: key (array-indeksen eller egenskapsnavnet), value (JSON-innholdet), og type (et tall som angir JSON-datatypen: 0=null, 1=streng, 2=tall, 3=boolsk, 4=array, 5=objekt).

Følgende spørring definerer et eksplisitt skjema for å trekke ut spesifikke kolonner med riktige datatyper:

SELECT 
    ProductID,
    ProductName,
    Price
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.id',
    ProductName NVARCHAR(100) '$.name',
    Price DECIMAL(10,2) '$.price'
);

Resultatsettet vil bli:

ProductID   ProductName   Price
---------   -----------   ------
1           Widget        29.99
2           Gadget        49.99
3           Gizmo         19.99

Klausulen WITH tilordner JSON-egenskaper til typede kolonner. Denne tilnærmingen gir deg riktige datatyper for beregninger og sammenligninger, og lar deg velge kun de egenskapene du trenger.

Kombiner OPENJSON med tabelldata ved å bruke CROSS APPLY:

-- Assuming Orders table has a JSON column called OrderDetails
SELECT 
    o.OrderID,
    o.CustomerID,
    items.ProductName,
    items.Quantity,
    items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
    ProductName NVARCHAR(100) '$.product',
    Quantity INT '$.qty',
    UnitPrice DECIMAL(10,2) '$.price'
) AS items;

Bemerkning

Når man bruker OPENJSON med CROSS APPLY, vises ikke rader fra hovedtabellen som har NULL eller tomme JSON-verdier i resultatene. Bruk OUTER APPLY hvis du trenger å inkludere rader uten JSON-data.

Konstruer JSON med `JSON_OBJECT` og `JSON_ARRAY`

SQL Server 2022 introdusert JSON_OBJECT og JSON_ARRAY funksjoner for intuitiv JSON-konstruksjon:

JSON_OBJECT() lager et JSON-objekt fra nøkkel-verdi-par, og følgende eksempel viser hvordan man bygger et JSON-objekt for et produkt:

SELECT JSON_OBJECT(
    'id': ProductID,
    'name': Name,
    'price': ListPrice,
    'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;

Resultatet vil bli:

ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}

Funksjonen håndterer automatisk datatypekonvertering og korrekt JSON-escape for spesialtegn i strengverdier.

JSON_ARRAY() lager et JSON-array fra verdier, og følgende eksempel bygger et JSON-array:

SELECT JSON_ARRAY(
    'SQL Server',
    'Azure SQL Database',
    'SQL Database in Fabric'
) AS Platforms;

Resultatet vil bli:

Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]

Du kan sende kolonneverdier, variabler eller bokstavelige verdier til JSON_ARRAY(). Funksjonen lager et korrekt formatert JSON-array uavhengig av input-typene.

Deretter kombinerer du disse funksjonene for å bygge nestede JSON-strukturer. Følgende eksempel konstruerer et komplett orden JSON-objekt med kunde- og totalinformasjon:

SELECT JSON_OBJECT(
    'orderId': soh.SalesOrderID,
    'orderDate': soh.OrderDate,
    'customer': JSON_OBJECT(
        'id': c.CustomerID,
        'name': c.CompanyName
    ),
    'totals': JSON_OBJECT(
        'subtotal': soh.SubTotal,
        'tax': soh.TaxAmt,
        'total': soh.TotalDue
    )
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
    ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;

Resultatet vil bli:

OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}

Å neste JSON_OBJECT kall skaper hierarkiske strukturer som matcher applikasjonens forventede format. Denne tilnærmingen er renere enn strengsammenkledning og sikrer gyldig JSON-utdata.

Aggregerte data med `JSON_ARRAYAGG`

JSON_ARRAYAGG samler verdier fra flere rader til et enkelt JSON-array. Denne funksjonen er nyttig for å lage denormalisert JSON-utdata fra normaliserte relasjonsdata:

SELECT 
    c.CustomerID,
    c.CompanyName,
    JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

Resultatet vil bli:

CustomerID   CompanyName           OrderIds
----------   -------------------   ------------------
29825        Contoso Retail        [71774,71776,71780]
29847        Adventure Works       [71782,71784]

Funksjonen samler alle matchende verdier fra de grupperte radene og kombinerer dem til et enkelt JSON-array. Dette er nyttig for å lage denormaliserte API-svar fra normaliserte databasetabeller.

Du kan kombinere JSON_ARRAYAGG med JSON_OBJECT for å lage matriser av komplekse objekter:

SELECT 
    pc.Name AS Category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id': p.ProductID,
            'name': p.Name,
            'price': p.ListPrice
        )
    ) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
    ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;

Følgende resultat vil være:

Category        Products
--------------  --------------------------------------------------------------------------
Road Bikes      [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes  [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]

Viktig!

JSON_ARRAYAGG og JSON_OBJECT/JSON_ARRAY funksjoner er tilgjengelige i SQL Server 2022 og senere, Azure SQL Database, og SQL-databaser i Microsoft Fabric. For tidligere versjoner, bruk FOR JSON PATH for lignende funksjonalitet.

Valider og sjekk JSON med `JSON_CONTAINS`

JSON-data fra eksterne kilder kan være feildannede, mangle forventede egenskaper, eller inneholde uventede verdier. Forsøk på å hente ut verdier fra ugyldig JSON eller manglende stier kan føre til spørringsfeil eller gi misvisende NULL resultater som skjuler dataproblemer.

Robust JSON-prosessering krever defensiv koding: valider at JSON-en er godt dannet før du parser den, sjekk at forventede stier eksisterer før du trekker ut verdier, og verifiser at verdiene samsvarer med forventningene dine før du bruker dem i forretningslogikk. SQL Server tilbyr flere funksjoner for å hjelpe deg med å validere JSON-innhold på hvert behandlingstrinn.

Forstå laxe vs strenge sti-moduser

Du kan bruke JSON-stiuttrykk i to moduser som styrer feilhåndtering:

DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';

-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;

-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;

Resultatet vil bli:

LaxResult
---------
NULL

-- Strict mode raises: Property cannot be found on the specified JSON path.

Bruk lax modus (standarden) når manglende egenskaper forventes og skal returnere NULL. Bruk strict modus når manglende egenskaper indikerer et dataproblem som bør gi en feil.

ISJSON validerer om en streng inneholder gyldig JSON. Følgende eksempel viser hvordan man bruker ISJSON:

SELECT 
    ISJSON('{"name": "test"}') AS ValidJson,      -- Returns 1
    ISJSON('not valid json') AS InvalidJson,       -- Returns 0
    ISJSON(NULL) AS NullJson;                      -- Returns NULL

Resultatet vil bli:

ValidJson   InvalidJson   NullJson
---------   -----------   --------
1           0             NULL

Bruk ISJSON in-klausuler WHERE for å filtrere rader med gyldig JSON, eller i CASE uttrykk for å håndtere ugyldige data på en elegant måte.

JSON_PATH_EXISTS sjekker om en spesifikk sti finnes i et JSON-dokument, som i følgende eksempel:

DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';

SELECT 
    JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
    JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;

Resultatet vil bli:

HasName   HasEmail
-------   --------
1         0

Denne funksjonen returnerer 1 hvis stien eksisterer, 0 hvis den ikke gjør det. Bruk det før du kaller JSON_VALUE i streng modus, eller for å betinget behandle JSON med varierende strukturer.

Bruk JSON_CONTAINS den for å sjekke om et JSON-dokument inneholder en spesifikk verdi eller et objekt, som i følgende eksempel:

DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';

SELECT 
    JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
    JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;

Resultatet vil bli:

HasSqlTag   HasPythonTag
---------   ------------
1           0

Optimaliser JSON-spørringer med beregnede kolonner

Når du ofte spør spesifikke JSON-egenskaper, må databasemotoren parse JSON-dokumentet for hver rad på hver spørring. For tabeller med tusenvis eller millioner av rader skaper denne gjentatte parsingen betydelig overhead. Beregnede kolonner lar deg hente ut JSON-verdier én gang og lagre dem i et spørrbart format som støtter indeksering.

Hvorfor JSON-parsing påvirker ytelsen

Tenk deg en tabell med 100 000 produktposter hvor hver rad inneholder et JSON-dokument med produktattributter. En spørringsfiltrering etter kategori må:

Les hver rad fra tabellen
Parse JSON-dokumentet for å finne kategoriegenskapen
Trekk ut og sammenlign verdien

Uten optimalisering krever selv enkle filtre fullstendige tabellskanninger med JSON-parsing på hver rad.

Lag beregnede kolonner for JSON-egenskaper

En beregnet kolonne trekker automatisk ut en JSON-egenskap og gjør den tilgjengelig som en regulær kolonne, som i følgende eksempel:

-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');

-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';

Resultatet vil bli:

ProductID   ProductName           ProductCategory
---------   -------------------   ---------------
101         Wireless Mouse        Electronics
102         USB Keyboard          Electronics
103         HD Monitor            Electronics

Som standard er de beregnede kolonnene virtuelle. Databasen beregner verdien ved spørringstidspunktet, men kan optimalisere JSON-ekstraksjonen. For enda bedre ytelse kan du beholde den beregnede kolonnen som i følgende eksempel:

-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;

Vedvarende kolonner lagrer den uttrukne verdien på disken, så JSON-en blir kun parset under INSERT og UPDATE operasjoner, ikke under SELECT spørringer.

Legg til indekser for raskere filtrering

Den reelle ytelsesgevinsten kommer fra indeksering av beregnede kolonner:

-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);

-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';

Uten indeksen skanner spørringen alle 100 000 rader. Med indeksen utfører spørringsmotoren en indekssøk og henter kun matchende rader. Dette kan redusere spørringstiden fra sekunder til millisekunder.

Indekser flere JSON-egenskaper

For spørringer som filtrerer på flere JSON-egenskaper, lag beregnede kolonner og en sammensatt indeks:

-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
    ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
    ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;

-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);

-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);

Nå kan spørringer som filtrerer etter kategori og merke, eller sorterer etter pris, bruke disse indeksene effektivt.

Tips

For ofte akkompagnerte JSON-egenskaper kan beregnede kolonner med indekser forbedre spørringsytelsen sammenlignet med å parse JSON under spørringstidspunktet. Overvåk spørringsmønstrene dine og lag beregnede kolonner for egenskaper brukt i WHERE, JOIN, eller ORDER BY klausuler.

Transformér relasjonsdata til JSON med FOR JSON

For omfattende JSON-utdata fra spørringer, bruk FOR JSON PATH eller FOR JSON AUTO:

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');

Resultatet vil bli:

{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}

FOR JSON PATH gir deg kontroll over JSON-strukturen gjennom kolonnealiaser. Bruk prikknotasjon i aliaser for å lage nestede objekter:

SELECT 
    p.ProductID AS 'product.id',
    p.Name AS 'product.name',
    pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;

Resultatet vil bli:

[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]

Kolonnealiaset 'product.id' oppretter et nestelt product objekt med en id egenskap. Denne teknikken lar deg forme utdataene slik at de matcher API-ens forventede format uten etterbehandling.

For mer informasjon om JSON-funksjoner i SQL Server, se JSON-data i SQL Server og JSON Functions.

Tilbakemeldinger

Var denne siden nyttig?

Behandle JSON-data med innebygde funksjoner

Trekk ut verdier med JSON_VALUE og JSON_QUERY

Parse JSON-arrays med OPENJSON

Konstruer JSON med JSON_OBJECT og JSON_ARRAY

Aggregerte data med JSON_ARRAYAGG

Valider og sjekk JSON med JSON_CONTAINS

Forstå laxe vs strenge sti-moduser

Optimaliser JSON-spørringer med beregnede kolonner

Hvorfor JSON-parsing påvirker ytelsen

Lag beregnede kolonner for JSON-egenskaper

Legg til indekser for raskere filtrering

Indekser flere JSON-egenskaper

Transformér relasjonsdata til JSON med FOR JSON

Tilbakemeldinger

Parse JSON-arrays med `OPENJSON`

Konstruer JSON med `JSON_OBJECT` og `JSON_ARRAY`

Aggregerte data med `JSON_ARRAYAGG`

Valider og sjekk JSON med `JSON_CONTAINS`