Bearbeta JSON-data med inbyggda funktioner

12 minuter

Tänk dig ett scenario där ditt e-handelsprogram lagrar kundinställningar och beställer metadata som JSON-dokument. En mobilapp skickar kundvagnsdata i JSON-format och ditt rapporteringssystem måste exportera produktkataloger som JSON för ett webb-API. Att arbeta direkt med JSON i databasen eliminerar behovet av transformeringar på programnivå och håller databearbetningen effektiv.

SQL Server-, Azure SQL- och SQL-databaser i Fabric har inbyggt JSON-stöd som gör att du kan parsa, fråga, skapa och transformera JSON-data direkt i T-SQL. I den här lektionen får du lära dig hur du använder JSON-funktioner för att extrahera värden, konstruera JSON-utdata, aggregera data i JSON-matriser och verifiera JSON-innehåll.

Extrahera värden med JSON_VALUE och JSON_QUERY

När du arbetar med JSON som lagras i databasen måste du extrahera specifika värden för filtrering, koppling eller visning. SQL Server har två funktioner för detta ändamål:

JSON_VALUE() extraherar ett skalärt värde (sträng, tal, booleskt värde) från en JSON-sträng:

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;

Resultatuppsättningen blir:

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

Funktionen navigerar i JSON-strukturen med hjälp av sökvägsuttrycket och returnerar värdet som en NVARCHAR(4000) sträng. Du kan omvandla resultatet till andra datatyper efter behov för beräkningar eller jämförelser.

JSON_QUERY() extraherar ett JSON-objekt eller en matris (icke-skalära värden):

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;

Resultatuppsättningen blir:

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

Till skillnad från JSON_VALUE()bevarar JSON_QUERY() bevarar JSON-strukturen, returnerar objekt och matriser som giltiga JSON-strängar som du kan lagra, skicka till andra funktioner eller återgå till program.

Sökvägsuttrycket använder $ för att representera rotelementet, med punktnotation för kapslade egenskaper och hakparentesnotation för arrayelement, som i följande exempel:

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

Resultatet blir:

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

Matrisindex börjar vid 0, så $.items[0] refererar till det första elementet. Använd den här syntaxen för att extrahera specifika objekt när du känner till positionen eller kombinera med OPENJSON när du behöver bearbeta alla matriselement.

Tips/Råd

Använd JSON_VALUE() när du behöver ett skalärt värde för jämförelser eller beräkningar. Använd JSON_QUERY() när du behöver bevara JSON-strukturen för kapslade objekt eller matriser.

Parsa JSON-matriser med `OPENJSON`

OPENJSON är en tabellvärdesfunktion som omvandlar JSON-data till en relationsraduppsättning. Använd den här funktionen för att koppla JSON-data till relationstabeller eller bearbeta matriselement individuellt.

Följande fråga parsar en JSON-matris i rader med standardschema:

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

Resultatuppsättningen blir:

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

Utan ett schema OPENJSON returnerar tre kolumner: key (matrisindexet eller egenskapsnamnet), value (JSON-innehållet) och type (ett tal som anger JSON-datatypen: 0=null, 1=sträng, 2=tal, 3=booleskt värde, 4=matris, 5=objekt).

Följande fråga definierar ett explicit schema för att extrahera specifika kolumner med rätt datatyper:

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

Resultatuppsättningen blir:

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

Satsen WITH mappar JSON-egenskaper till inskrivna kolumner. Med den här metoden får du rätt datatyper för beräkningar och jämförelser, och du kan bara välja de egenskaper du behöver.

Kombinera OPENJSON med tabelldata med :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;

Anmärkning

När du använder OPENJSON med CROSS APPLYvisas inte rader från huvudtabellen med NULL eller tomma JSON-värden i resultatet. Använd OUTER APPLY om du behöver inkludera rader utan JSON-data.

Skapa JSON med `JSON_OBJECT` och `JSON_ARRAY`

SQL Server 2022 introducerade funktionerna JSON_OBJECT och JSON_ARRAY för intuitiv JSON-konstruktion:

JSON_OBJECT() skapar ett JSON-objekt från nyckel/värde-par. I följande exempel visas hur du skapar ett JSON-objekt för en 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 blir:

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

Funktionen hanterar automatiskt datatypskonvertering och korrekt JSON-escaping för specialtecken i strängvärden.

JSON_ARRAY() skapar en JSON-matris från värden, skapar följande exempel en JSON-matris:

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

Resultatet blir:

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

Du kan skicka kolumnvärden, variabler eller literalvärden till JSON_ARRAY(). Funktionen skapar en korrekt formaterad JSON-matris oavsett indatatyper.

Kombinera sedan dessa funktioner för att skapa kapslade JSON-strukturer. I följande exempel konstrueras ett fullständigt JSON-orderobjekt med kund- och totalinformation:

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 blir:

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

Inbäddning av JSON_OBJECT-anrop skapar hierarkiska strukturer som matchar din applikations förväntade format. Den här metoden är renare än strängsammanfogning och garanterar giltiga JSON-utdata.

Aggregera data med `JSON_ARRAYAGG`

JSON_ARRAYAGG aggregerar värden från flera rader till en enda JSON-matris. Den här funktionen är användbar för att skapa avnormaliserade JSON-utdata från normaliserade relationsdata:

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 blir:

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

Funktionen samlar in alla matchande värden från de grupperade raderna och kombinerar dem till en enda JSON-matris. Detta är användbart för att skapa avnormaliserade API-svar från normaliserade databastabeller.

Du kan kombinera JSON_ARRAYAGG med JSON_OBJECT för att skapa matriser med komplexa objekt:

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öljande resultat blir:

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

Viktigt!

JSON_ARRAYAGG och JSON_OBJECT/JSON_ARRAY funktioner är tillgängliga i SQL Server 2022 och senare, Azure SQL Database och SQL-databaser i Microsoft Fabric. För tidigare versioner använder du FOR JSON PATH för liknande funktioner.

Verifiera och kontrollera JSON med `JSON_CONTAINS`

JSON-data från externa källor kan vara felaktiga, saknade förväntade egenskaper eller innehålla oväntade värden. Försök att extrahera värden från ogiltig JSON eller sökvägar som saknas kan orsaka frågefel eller returnera missvisande NULL resultat som maskerar dataproblem.

Robust JSON-bearbetning kräver defensiv kodning: verifiera att JSON är välformulerad innan du parsar den, kontrollera att det finns förväntade sökvägar innan du extraherar värden och kontrollera att värdena matchar dina förväntningar innan du använder dem i affärslogik. SQL Server innehåller flera funktioner som hjälper dig att verifiera JSON-innehåll i varje bearbetningssteg.

Förstå släpphänta respektive strikta sökvägslägen

Du kan använda JSON-sökvägsuttryck i två lägen som styr felhanteringen:

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 blir:

LaxResult
---------
NULL

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

Använd lax läge (standard) när saknade egenskaper förväntas och ska returnera NULL. Använd strict-läget när saknade egenskaper indikerar ett dataproblem som bör generera ett fel.

ISJSON verifierar om en sträng innehåller giltig JSON. I följande exempel visas hur du använder ISJSON:

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

Resultatet blir:

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

Använd ISJSON i WHERE -satser för att filtrera rader med giltig JSON eller i CASE uttryck för att hantera ogiltiga data korrekt.

JSON_PATH_EXISTS kontrollerar om det finns en specifik sökväg i ett JSON-dokument, som i följande exempel:

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 blir:

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

Den här funktionen returnerar 1 om sökvägen finns, 0 om den inte gör det. Använd den innan du anropar JSON_VALUE i strikt läge eller för att villkorligt bearbeta JSON med varierande strukturer.

Använd JSON_CONTAINS för att kontrollera om ett JSON-dokument innehåller ett visst värde eller objekt, som i följande exempel:

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 blir:

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

Optimera JSON-frågor med beräknade kolumner

När du ofta frågar efter specifika JSON-egenskaper måste databasmotorn parsa JSON-dokumentet för varje rad i varje fråga. För tabeller med tusentals eller miljontals rader skapar den här upprepade parsningen betydande omkostnader. Med beräknade kolumner kan du extrahera JSON-värden en gång och lagra dem i ett frågeformat som stöder indexering.

Varför JSON-parsning påverkar prestanda

Överväg en tabell med 100 000 produktposter där varje rad innehåller ett JSON-dokument med produktattribut. En frågefiltrering efter kategori måste:

Läsa varje rad från tabellen
Parsa JSON-dokumentet för att hitta kategoriegenskapen
Extrahera och jämför värdet

Utan optimering kräver även enkla filter fullständiga tabellgenomsökningar med JSON-parsning på varje rad.

Skapa beräknade kolumner för JSON-egenskaper

En beräknad kolumn extraherar automatiskt en JSON-egenskap och gör den tillgänglig som en vanlig kolumn, som i följande exempel:

-- 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 blir:

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

Som standard är beräknade kolumner virtuella. Databasen beräknar värdet vid frågetillfället men kan optimera JSON-extrahering. För ännu bättre prestanda kan du spara den beräknade kolumnen som i följande exempel:

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

Bevarade kolumner lagrar det extraherade värdet på disken, så JSON parsas endast under INSERT och UPDATE processer, inte under SELECT sökfrågor.

Lägga till index för snabbare filtrering

Den verkliga prestandavinsten kommer från indexering av beräknade kolumner:

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

Utan indexet söker frågan igenom alla 100 000 rader. Med indexet utför frågemotorn en indexsökning och hämtar endast matchande rader. Detta kan minska frågetiden från sekunder till millisekunder.

Indexera flera JSON-egenskaper

För frågor som filtrerar på flera JSON-egenskaper skapar du beräknade kolumner och ett sammansatt index:

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

Nu kan frågor som filtrerar efter kategori och varumärke, eller sortering efter pris, använda dessa index effektivt.

Tips/Råd

För JSON-egenskaper som används ofta kan beräknade kolumner med index förbättra frågeprestanda jämfört med att parsa JSON vid frågetillfället. Övervaka dina frågemönster och skapa beräknade kolumner för egenskaper som används i WHERE, JOINeller ORDER BY -satser.

Transformera relationsdata till JSON med FOR JSON

För omfattande JSON-utdata från frågor använder du 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 blir:

{"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 ger dig kontroll över JSON-strukturen via kolumnalias. Använd punkt notation i alias för att skapa kapslade objekt:

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 blir:

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

Kolumnaliaset 'product.id' skapar ett kapslat product objekt med en id egenskap. Med den här tekniken kan du forma utdata så att de matchar ditt API:s förväntade format utan efterbearbetning.

Mer information om JSON-funktioner i SQL Server finns i JSON-data i SQL Server och JSON Functions.

Feedback

Var den här sidan till hjälp?

Bearbeta JSON-data med inbyggda funktioner

Extrahera värden med JSON_VALUE och JSON_QUERY

Parsa JSON-matriser med OPENJSON

Skapa JSON med JSON_OBJECT och JSON_ARRAY

Aggregera data med JSON_ARRAYAGG

Verifiera och kontrollera JSON med JSON_CONTAINS

Förstå släpphänta respektive strikta sökvägslägen

Optimera JSON-frågor med beräknade kolumner

Varför JSON-parsning påverkar prestanda

Skapa beräknade kolumner för JSON-egenskaper

Lägga till index för snabbare filtrering

Indexera flera JSON-egenskaper

Transformera relationsdata till JSON med FOR JSON

Feedback

Parsa JSON-matriser med `OPENJSON`

Skapa JSON med `JSON_OBJECT` och `JSON_ARRAY`

Aggregera data med `JSON_ARRAYAGG`

Verifiera och kontrollera JSON med `JSON_CONTAINS`