Zpracování dat JSON pomocí předdefinovaných funkcí

Představte si scénář, ve kterém vaše aplikace elektronického obchodování ukládá předvolby zákazníků a metadata objednávek jako dokumenty JSON. Mobilní aplikace odesílá data nákupního košíku ve formátu JSON a váš systém generování sestav musí exportovat katalogy produktů jako JSON pro webové rozhraní API. Práce přímo s JSON ve vaší databázi eliminuje potřebu transformací aplikační vrstvy a zajišťuje efektivní zpracování dat.

Databáze SQL Server, Azure SQL a SQL ve službě Fabric poskytují integrovanou podporu JSON, která umožňuje analyzovat, dotazovat, vytvářet a transformovat data JSON přímo v T-SQL. V této lekci se dozvíte, jak pomocí funkcí JSON extrahovat hodnoty, vytvořit výstup JSON, agregovat data do polí JSON a ověřit obsah JSON.

Extrahování hodnot pomocí JSON_VALUE a JSON_QUERY

Při práci s JSON uloženými v databázi musíte extrahovat konkrétní hodnoty pro filtrování, spojování nebo zobrazení. SQL Server poskytuje pro tento účel dvě funkce:

JSON_VALUE() extrahuje skalární hodnotu (řetězec, číslo, logická hodnota) z řetězce JSON:

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;

Výsledek bude:

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

Funkce prochází strukturu JSON pomocí výrazu 'cesta' a vrátí hodnotu jako NVARCHAR(4000) řetězec. Výsledek můžete přetypovat na jiné datové typy podle potřeby pro výpočty nebo porovnání.

JSON_QUERY() extrahuje objekt NEBO pole JSON (nekalarní hodnoty):

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;

Výsledek bude:

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

Na rozdíl od JSON_VALUE()toho JSON_QUERY() zachovává strukturu JSON, vrací objekty a pole jako platné řetězce JSON, které můžete ukládat, předávat jiným funkcím nebo se vracet do aplikací.

Výraz cesty používá $ k reprezentaci kořenového prvku, s tečkovou notací pro vnořené vlastnosti a zápisem hranatých závorek pro prvky pole, jako je v následujícím příkladu:

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

Výsledkem bude:

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

Indexy pole začínají na 0, takže $.items[0] odkazuje na první prvek. Tuto syntaxi použijte k extrahování konkrétních položek, když znáte jejich pozici, nebo ji zkombinujte s OPENJSON, když potřebujete zpracovat všechny prvky pole.

Parsování polí JSON pomocí OPENJSON

OPENJSON je funkce s hodnotou tabulky, která transformuje data JSON na relační sadu řádků. Pomocí této funkce můžete spojit data JSON s relačními tabulkami nebo zpracovat prvky pole jednotlivě.

Následující dotaz parsuje pole JSON do řádků s výchozím schématem:

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

Výsledek bude:

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

Bez schématu OPENJSON vrátí tři sloupce: key (název indexu pole nebo vlastnosti), value (obsah JSON) a type (číslo označující datový typ JSON: 0=null, 1=string, 2=number, 3=boolean, 4=array, 5=object).

Následující dotaz definuje explicitní schéma pro extrahování konkrétních sloupců se správnými datovými typy:

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

Výsledek bude:

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

Klauzule WITH mapuje vlastnosti JSON na zadané sloupce. Tento přístup poskytuje správné datové typy pro výpočty a porovnání a umožňuje vybrat pouze vlastnosti, které potřebujete.

Kombinování OPENJSON s tabulkovými daty pomocí 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;

Vytvoření FORMÁTU JSON pomocí JSON_OBJECT a JSON_ARRAY

SQL Server 2022 představil JSON_OBJECT a JSON_ARRAY funkce pro intuitivní konstrukci JSON:

JSON_OBJECT() vytvoří objekt JSON z párů klíč-hodnota. Následující příklad ukazuje, jak vytvořit objekt JSON pro 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;

Výsledkem bude:

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

Funkce automaticky zpracovává převod datového typu a správné escapování JSON pro speciální znaky v řetězcových hodnotách.

JSON_ARRAY() vytvoří pole JSON z hodnot. Následující příklad vytvoří pole JSON:

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

Výsledkem bude:

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

Můžete předat hodnoty sloupců, proměnné nebo literály do JSON_ARRAY(). Funkce vytvoří správně naformátované pole JSON bez ohledu na vstupní typy.

Potom tyto funkce zkombinujte a sestavte vnořené struktury JSON. Následující příklad vytvoří kompletní objekt JSON objednávky s informacemi o zákaznících a součtech:

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;

Výsledkem bude:

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

Volání funkcí JSON_OBJECT vytváří hierarchické struktury, které odpovídají očekávanému formátu vaší aplikace. Tento přístup je čistější než zřetězení řetězců a zajišťuje platný výstup JSON.

Agregace dat pomocí JSON_ARRAYAGG

JSON_ARRAYAGG agreguje hodnoty z více řádků do jednoho pole JSON. Tato funkce je užitečná při vytváření denormalizovaného výstupu JSON z normalizovaných relačních dat:

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;

Výsledkem bude:

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

Funkce shromažďuje všechny odpovídající hodnoty ze seskupených řádků a kombinuje je do jednoho pole JSON. Tato metoda je užitečná při vytváření denormalizovaných odpovědí rozhraní API z normalizovaných databázových tabulek.

Pomocí kombinace JSON_ARRAYAGGJSON_OBJECT můžete vytvářet pole komplexních 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;

Následující výsledek bude:

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

Ověření a kontrola FORMÁTU JSON pomocí JSON_CONTAINS

Data JSON z externích zdrojů můžou být poškozená, chybí očekávané vlastnosti nebo obsahují neočekávané hodnoty. Pokus o extrakci hodnot z neplatného formátu JSON nebo chybějících cest může způsobit selhání dotazů nebo vrátit zavádějící NULL výsledky, které maskují problémy s daty.

Robustní zpracování JSON vyžaduje defenzivní kódování: Před parsováním ověřte, že je JSON správně vytvořený. Před extrahováním hodnot zkontrolujte, jestli existují očekávané cesty, a před použitím obchodní logiky ověřte, že hodnoty odpovídají vašim očekáváním. SQL Server poskytuje několik funkcí, které vám pomůžou ověřit obsah JSON v každé fázi zpracování.

Principy laxních a striktních režimů cest

Výrazy cesty JSON můžete použít ve dvou režimech, které řídí zpracování chyb:

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;

Výsledkem bude:

LaxResult
---------
NULL

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

Použijte režim lax (výchozí) v případech, kdy se očekává, že budou některé vlastnosti chybět, a mělo by se vrátit NULL. Použijte režim strict, pokud chybějící vlastnosti označují problém s daty, který by měl vyvolat chybu.

ISJSON ověří, jestli řetězec obsahuje platný JSON. Následující příklad ukazuje, jak používat ISJSON:

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

Výsledkem bude:

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

K řádnému zpracování neplatných dat použijte ISJSON klauzule v WHERE klauzulích k filtrování řádků s platným kódem JSON nebo ve CASE výrazech.

JSON_PATH_EXISTS zkontroluje, jestli v dokumentu JSON existuje konkrétní cesta, například v následujícím příkladu:

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;

Výsledkem bude:

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

Tato funkce vrátí hodnotu 1, pokud cesta existuje, 0, pokud neexistuje. Použijte ho před voláním JSON_VALUE v přísném režimu nebo pro podmíněné zpracovávání JSON s různými strukturami.

Slouží JSON_CONTAINS ke kontrole, jestli dokument JSON obsahuje konkrétní hodnotu nebo objekt, například v následujícím příkladu:

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

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

Výsledkem bude:

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

Optimalizace dotazů JSON pomocí počítaných sloupců

Když často dotazujete konkrétní vlastnosti JSON, databázový stroj musí analyzovat dokument JSON pro každý řádek každého dotazu. U tabulek s tisíci nebo miliony řádků tato opakovaná analýza vytváří významnou režii. Počítané sloupce umožňují extrahovat hodnoty JSON jednou a uložit je do dotazovatelného formátu, který podporuje indexování.

Proč analýza JSON ovlivňuje výkon

Představte si tabulku s 100 000 záznamy o produktech, kde každý řádek obsahuje dokument JSON s atributy produktu. Filtrování dotazu podle kategorie musí:

1. Přečtěte každý řádek z tabulky
2. Pokud chcete najít vlastnost kategorie, parsujte dokument JSON.
3. Extrahujte a porovnejte hodnotu

Bez optimalizace vyžadují i jednoduché filtry úplné prohledávání tabulek s parsováním JSON na každém řádku.

Vytvoření počítaných sloupců pro vlastnosti JSON

Počítaný sloupec automaticky extrahuje vlastnost JSON a zpřístupní ji jako běžný sloupec, například v následujícím příkladu:

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

Výsledkem bude:

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

Ve výchozím nastavení jsou vypočítané sloupce virtuální. Databáze vypočítá hodnotu v době dotazu, ale dokáže optimalizovat extrakci JSON. Pokud chcete dosáhnout ještě lepšího výkonu, můžete počítaný sloupec zachovat jako v následujícím příkladu:

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

Trvalé sloupce ukládají extrahované hodnoty na disk, takže JSON se parsuje jenom během INSERT a UPDATE operacích, ne během SELECT dotazů.

Přidání indexů pro rychlejší filtrování

Skutečný zisk z výkonu pochází z indexování vypočítaných sloupců:

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

Bez indexu dotaz prohledá všech 100 000 řádků. Pomocí indexu dotazovací modul provede hledání indexu a načte pouze odpovídající řádky. Tento index může zkrátit dobu dotazu z sekund na milisekundy.

Indexování více vlastností JSON

Pro dotazy, které filtrují více vlastností JSON, vytvořte počítané sloupce a složený 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);

Teď dotazy filtrující podle kategorie a značky nebo řazení podle ceny můžou tyto indexy efektivně používat.

Transformace relačních dat na JSON pomocí formátu FOR JSON

Pro komplexní výstup JSON z dotazů použijte:FOR JSON PATHFOR 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');

Výsledkem bude:

{"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 poskytuje kontrolu nad strukturou JSON prostřednictvím aliasů sloupců. K vytvoření vnořených objektů použijte zápis tečky v aliasech:

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;

Výsledkem bude:

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

Alias 'product.id' sloupce vytvoří vnořený product objekt s id vlastností. Tato technika umožňuje tvarovat výstup tak, aby odpovídal očekávanému formátu rozhraní API bez následného zpracování.

Další informace o funkcích JSON na SQL Serveru najdete v tématu Data JSON v SQL Serveru a funkcích JSON.