Weryfikowanie, wykonywanie zapytań i zmienianie danych JSON za pomocą wbudowanych funkcji

Dotyczy do: SQL Server 2016 (13.x) i nowsze wersje Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Wbudowana obsługa formatu JSON w Aparacie bazy danych SQL obejmuje następujące funkcje:

Kod ISJSON sprawdza, czy ciąg zawiera prawidłowy kod JSON.
JSON_VALUE wyodrębnia wartość skalarną z ciągu JSON.
JSON_QUERY wyodrębnia obiekt lub tablicę z ciągu JSON.
JSON_MODIFY aktualizuje wartość właściwości w ciągu JSON i zwraca zaktualizowany ciąg JSON.

W przypadku wszystkich funkcji JSON zapoznaj się z artykułem Funkcje JSON (Transact-SQL).

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2025 lub AdventureWorksDW2025, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

Tekst przykładów JSON dla tej strony

Przykłady na tej stronie używają tekstu JSON podobnego do zawartości przedstawionej w poniższym przykładzie:

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

Ten dokument JSON, który zawiera zagnieżdżone elementy złożone, jest przechowywany w następującej przykładowej tabeli:

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

Funkcje JSON działają tak samo, czy dokument JSON jest przechowywany w formacie varchar, nvarchar, czy natywnym typem danych json .

Weryfikowanie tekstu JSON przy użyciu funkcji ISJSON

Funkcja ISJSON sprawdza, czy ciąg zawiera prawidłowy kod JSON.

Poniższy przykład zwraca wiersze, w których kolumna JSON zawiera prawidłowy tekst JSON. Bez jawnego ograniczenia JSON można wprowadzić dowolny tekst w kolumnie nvarchar :

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

Aby uzyskać więcej informacji, zobacz ISJSON (Transact-SQL).

Wyodrębnianie wartości z tekstu JSON przy użyciu funkcji JSON_VALUE

Funkcja JSON_VALUE wyodrębnia wartość skalarną z ciągu JSON. Następujące zapytanie zwraca dokumenty, w których id pole JSON jest zgodne z wartością DesaiFamily, uporządkowaną według city i state polami 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

Wyniki tego zapytania przedstawiono w poniższej tabeli:

Name	City	County
`DesaiFamily`	`NY`	`Manhattan`

Aby uzyskać więcej informacji, zobacz JSON_VALUE.

Wyodrębnianie obiektu lub tablicy z tekstu JSON przy użyciu funkcji JSON_QUERY

Funkcja JSON_QUERY wyodrębnia obiekt lub tablicę z ciągu JSON. W poniższym przykładzie pokazano, jak zwrócić fragment JSON w wynikach zapytania.

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

Wyniki tego zapytania przedstawiono w poniższej tabeli:

Address	Parents	Element nadrzędny0
`{ "state": "NY", "county": "Manhattan", "city": "NY" }`	`[ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ]`	`{ "familyName": "Desai", "givenName": "Prashanth" }`

Aby uzyskać więcej informacji, zobacz JSON_QUERY.

Analizowanie zagnieżdżonych kolekcji JSON

OPENJSON funkcja umożliwia przekształcanie podarray JSON w zestaw wierszy, a następnie łączenie go z elementem nadrzędnym. Na przykład można zwrócić wszystkie dokumenty rodziny i "połączyć" je z ich obiektami children, które są przechowywane jako wewnętrzna tablica 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

Wyniki tego zapytania przedstawiono w poniższej tabeli:

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

Zwracane są dwa wiersze, ponieważ jeden wiersz macierzysty jest sprzężony z dwoma wierszami podrzędnymi uzyskanymi przez analizę dwóch elementów tablicy podrzędnej. OPENJSON funkcja analizuje children fragment z doc kolumny i zwraca grade i givenName z każdego elementu jako zestaw wierszy. Ten zestaw wierszy można połączyć z dokumentem nadrzędnym.

Zapytanie zagnieżdżonych hierarchicznych podtablic JSON

Aby wykonywać zapytania na zagnieżdżonych strukturach JSON, można zastosować wiele wywołań CROSS APPLY OPENJSON. Dokument JSON używany w tym przykładzie zawiera zagnieżdżoną tablicę o nazwie children, w której każdy element podrzędny ma zagnieżdżoną tablicę pets. Następujące zapytanie analizuje dzieci z każdego dokumentu, zwraca każdy element tablicy jako wiersz, a następnie analizuje tablicę pets.

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;

Pierwsze OPENJSON wywołanie zwraca fragment tablicy przy użyciu klauzuli children AS JSON. Ten fragment tablicy jest dostarczany do drugiej OPENJSON funkcji, która zwraca givenName element, firstName każdego dziecka, a także tablicę pets. Tablica pets jest przekazywana do trzeciej OPENJSON funkcji, która zwraca givenName zwierzaka.

Wyniki tego zapytania przedstawiono w poniższej tabeli:

familyName	imię dziecka	nazwaZwierzęcia
`Desai`	`Jesse`	`Goofy`
`Desai`	`Jesse`	`Shadow`
`Desai`	`Lisa`	`NULL`

Dokument główny jest połączony z dwoma children wierszami zwracanymi przez pierwsze OPENJSON(children) wywołanie tworzące dwa wiersze (lub krotki). Następnie każdy wiersz jest sprzężony z nowymi wierszami wygenerowanymi przy użyciu OPENJSON(pets)OUTER APPLY operatora . Jesse ma dwa zwierzęta domowe, więc (Desai, Jesse) jest połączony z dwoma wierszami wygenerowanymi dla Goofy i Shadow. Lisa nie ma zwierząt domowych, więc nie ma wierszy zwracanych przez OPENJSON(pets) dla tej krotki. Jednak ponieważ używamy OUTER APPLY, uzyskujemy NULL w kolumnie. Jeśli umieścimy CROSS APPLY zamiast OUTER APPLY, Lisa nie zostanie zwrócona w wyniku, ponieważ nie ma żadnych wierszy dotyczących zwierząt, które mogą być połączone z tą krotką.

Porównanie JSON_VALUE i JSON_QUERY

Kluczową różnicą między elementami JSON_VALUE i JSON_QUERY jest zwracanie JSON_VALUE wartości skalarnej, a funkcja JSON_QUERY zwraca obiekt lub tablicę.

Rozważmy następujący przykładowy tekst JSON.

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

W tym przykładowym tekście JSON składowe danych "a" i "c" są wartościami ciągów, a element członkowski danych "b" jest tablicą. JSON_VALUE i JSON_QUERY zwracają następujące wyniki:

Path	Zwraca wartość `JSON_VALUE`.	Zwraca wartość `JSON_QUERY`.
`$`	`NULL` lub błąd	`{ "a": "[1,2]", "b": [1, 2], "c": "hi" }`
`$.a`	`[1,2]`	`NULL` lub błąd
`$.b`	`NULL` lub błąd	`[1,2]`
`$.b[0]`	`1`	`NULL` lub błąd
`$.c`	`hi`	`NULL` lub błąd

Testowanie JSON_VALUE i JSON_QUERY za pomocą przykładowej bazy danych AdventureWorks

Przetestuj wbudowane funkcje opisane w tym artykule, uruchamiając następujące przykłady z przykładową bazą AdventureWorks2025 danych. Aby uzyskać więcej informacji na temat dodawania danych JSON do testowania przez uruchomienie skryptu, zobacz Obsługa wbudowanego kodu JSON dla dysku testowego.

W poniższych przykładach kolumna Info w SalesOrder_json tabeli zawiera tekst JSON.

Przykład 1 — zwracanie zarówno standardowych kolumn, jak i danych JSON

Poniższe zapytanie zwraca wartości zarówno ze standardowych kolumn relacyjnych, jak i z kolumny 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;

Przykład 2 — Agregowanie i filtrowanie wartości JSON

Następujące zapytanie agreguje sumy częściowe według nazwy klienta (przechowywanej w formacie JSON) i stanu (przechowywanego w zwykłej kolumnie). Następnie filtruje wyniki według miasta (przechowywanego w formacie JSON) i OrderDate (przechowywanego w zwykłej kolumnie).

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;

Aktualizowanie wartości właściwości w tekście JSON przy użyciu funkcji JSON_MODIFY

Funkcja JSON_MODIFY aktualizuje wartość właściwości w ciągu JSON i zwraca zaktualizowany ciąg JSON.

Poniższy przykład aktualizuje wartość właściwości JSON w zmiennej zawierającej kod JSON.

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

Aby uzyskać więcej informacji, zobacz JSON_MODIFY.

Sprzężenie zwrotne

Czy ta strona była pomocna?

Last updated on 2025-11-18

Udostępnij za pomocą

Weryfikowanie, wykonywanie zapytań i zmienianie danych JSON za pomocą wbudowanych funkcji

Tekst przykładów JSON dla tej strony

Weryfikowanie tekstu JSON przy użyciu funkcji ISJSON

Wyodrębnianie wartości z tekstu JSON przy użyciu funkcji JSON_VALUE

Wyodrębnianie obiektu lub tablicy z tekstu JSON przy użyciu funkcji JSON_QUERY

Analizowanie zagnieżdżonych kolekcji JSON

Zapytanie zagnieżdżonych hierarchicznych podtablic JSON

Porównanie JSON_VALUE i JSON_QUERY

Testowanie JSON_VALUE i JSON_QUERY za pomocą przykładowej bazy danych AdventureWorks

Przykład 1 — zwracanie zarówno standardowych kolumn, jak i danych JSON

Przykład 2 — Agregowanie i filtrowanie wartości JSON

Aktualizowanie wartości właściwości w tekście JSON przy użyciu funkcji JSON_MODIFY

Treści powiązane

Sprzężenie zwrotne

Dodatkowe zasoby