Ověřování, dotazování a změna dat JSON pomocí předdefinovaných funkcí

Platí na: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Integrovaná podpora KÓDU JSON v databázovém stroji SQL obsahuje následující funkce:

• ISJSON testuje, jestli řetězec obsahuje platný KÓD JSON.
• JSON_VALUE extrahuje skalární hodnotu z řetězce JSON.
• JSON_QUERY extrahuje objekt nebo pole z řetězce JSON.
• JSON_MODIFY aktualizuje hodnotu vlastnosti v řetězci JSON a vrátí aktualizovaný řetězec JSON.

Pro všechny funkce JSON zkontrolujte funkce JSON (Transact-SQL).

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2025 nebo AdventureWorksDW2025, kterou si můžete stáhnout z domovské stránky Microsoft SQL Serveru pro ukázky a komunitní projekty .

Text JSON pro příklady na této stránce

Příklady na této stránce používají text JSON podobný obsahu uvedenému v následujícím příkladu:

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

Tento dokument JSON, který obsahuje vnořené komplexní prvky, je uložený v následující ukázkové tabulce:

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

Funkce JSON fungují stejně, jako když je dokument JSON uložený v souboru varchar, nvarchar nebo nativním datovém typu JSON .

Ověření textu JSON pomocí funkce ISJSON

Funkce ISJSON testuje, jestli řetězec obsahuje platný kód JSON.

Následující příklad vrátí řádky, ve kterých sloupec JSON obsahuje platný text JSON. Bez explicitního omezení JSON můžete do sloupce nvarchar zadat libovolný text:

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

Další informace naleznete v tématu ISJSON (Transact-SQL).

Extrahování hodnoty z textu JSON pomocí funkce JSON_VALUE

Funkce JSON_VALUE extrahuje skalární hodnotu z řetězce JSON. Následující dotaz vrátí dokumenty, ve kterých id pole JSON odpovídá hodnotě DesaiFamilyseřazené podle city polí JSON a state JSON:

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

Výsledky tohoto dotazu jsou uvedené v následující tabulce:

Name	City	County
DesaiFamily	NY	Manhattan

Další informace najdete v tématu JSON_VALUE.

Extrahování objektu nebo pole z textu JSON pomocí funkce JSON_QUERY

Funkce JSON_QUERY extrahuje objekt nebo pole z řetězce JSON. Následující příklad ukazuje, jak vrátit fragment JSON ve výsledcích dotazu.

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

Výsledky tohoto dotazu jsou uvedené v následující tabulce:

Address	Parents	Nadřazený objekt 0
{ "state": "NY", "county": "Manhattan", "city": "NY" }	[ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ]	{ "familyName": "Desai", "givenName": "Prashanth" }

Další informace najdete v tématu JSON_QUERY.

Analýza vnořených kolekcí JSON

OPENJSON funkce umožňuje transformovat podarray JSON do sady řádků a pak ji spojit s nadřazeným prvkem. Můžete například vrátit všechny rodinné dokumenty a připojit se k nim pomocí objektů children uložených jako vnitřní pole JSON:

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

Výsledky tohoto dotazu jsou uvedené v následující tabulce:

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

Vrátí se dva řádky, protože jeden nadřazený řádek je spojený se dvěma podřízenými řádky vytvořenými parsováním dvou prvků podřízeného dílčího pole. OPENJSON funkce parsuje children fragment ze doc sloupce a vrací grade a givenName z každého prvku jako sadu řádků. Tuto sadu řádků lze spojit s nadřazeným dokumentem.

Dotaz na vnořená hierarchická podpole JSON

K dotazování vnořených struktur JSON můžete použít více CROSS APPLY OPENJSON volání. Dokument JSON použitý v tomto příkladu má vnořené pole s názvem children, kde každý potomek má vnořené pole pets. Následující dotaz analyzuje podřízené položky z každého dokumentu, vrátí každý objekt pole jako řádek a pak parsuje pets pole:

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;

První OPENJSON volání vrátí fragment children pole pomocí klauzule AS JSON. Tento fragment pole je poskytován druhé OPENJSON funkci, která vrací givenName, firstName každého podřízeného, stejně jako pole pets. Pole pets je poskytováno té třetí OPENJSON funkci, která vrací givenName peta.

Výsledky tohoto dotazu jsou uvedené v následující tabulce:

familyName	childGivenName	jméno domácího mazlíčka
Desai	Jesse	Goofy
Desai	Jesse	Shadow
Desai	Lisa	NULL

Kořenový dokument je spojen se dvěma řádky children, které byly vráceny prvním voláním OPENJSON(children), čímž vzniknou dvě řádky (nebo n-tice). Každý řádek se pak spojí s novými řádky vygenerovanými pomocí OPENJSON(pets)OUTER APPLY operátoru. Jesse má dva domácí zvířata, takže (Desai, Jesse) je spojené se dvěma řádky vygenerovanými pro Goofy a Shadow. Lisa nemá domácí zvířata, takže pro tuto n-tici nejsou žádné záznamy vráceny prostřednictvím OPENJSON(pets). Vzhledem k tomu, že používáme OUTER APPLY, obdržíme NULL ve sloupci. Pokud dáme CROSS APPLY místo OUTER APPLY, Lisa nebude vrácena ve výsledku, protože neexistují žádné řádky domácích zvířat, které by mohly být spojeny s touto n-ticí.

Porovnání JSON_VALUE a JSON_QUERY

Klíčový rozdíl mezi JSON_VALUE a JSON_QUERY je, že JSON_VALUE vrací skalární hodnotu, zatímco JSON_QUERY vrací objekt nebo pole.

Podívejte se na následující ukázkový text JSON.

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

V tomto ukázkovém textu JSON jsou datové členy "a" a "c" řetězcové hodnoty, zatímco datový člen "b" je pole. JSON_VALUE a JSON_QUERY vrátí následující výsledky:

Path	JSON_VALUE vrací	JSON_QUERY vrací
$	NULL nebo chyba	{ "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a	[1,2]	NULL nebo chyba
$.b	NULL nebo chyba	[1,2]
$.b[0]	1	NULL nebo chyba
$.c	hi	NULL nebo chyba

Testování JSON_VALUE a JSON_QUERY s ukázkovou databází AdventureWorks

Otestujte předdefinované funkce popsané v tomto článku spuštěním následujících příkladů ukázkové AdventureWorks2025 databáze. Další informace o tom, jak přidat data JSON pro testování spuštěním skriptu, najdete v tématu Podpora integrovaného testovacího disku JSON.

V následujících příkladech Info obsahuje sloupec v SalesOrder_json tabulce text JSON.

Příklad 1 – Vrácení standardních sloupců i dat JSON

Následující dotaz vrátí hodnoty ze standardních relačních sloupců i ze sloupce JSON.

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;

Příklad 2– Agregace a filtrování hodnot JSON

Následující dotaz agreguje mezisoučty podle jména zákazníka (uloženého ve formátu JSON) a stavu (uloženého v běžném sloupci). Pak filtruje výsledky podle města (uloženého ve formátu JSON) a OrderDate (uložené v běžném sloupci).

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;

Aktualizace hodnot vlastností v textu JSON pomocí funkce JSON_MODIFY

Funkce JSON_MODIFY aktualizuje hodnotu vlastnosti v řetězci JSON a vrátí aktualizovaný řetězec JSON.

Následující příklad aktualizuje hodnotu vlastnosti JSON v proměnné, která obsahuje JSON.

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

Další informace najdete v tématu JSON_MODIFY.

Související obsah

• ISJSON (Transact-SQL)
• JSON_VALUE (Transact-SQL)
• JSON_QUERY (Transact-SQL)
• JSON_MODIFY (Transact-SQL)
• Výrazy cesty JSON
• Datový typ JSON