Udostępnij za pośrednictwem

JSON_MODIFY (Transact-SQL)

  • Artykuł

Dotyczy: programu SQL Server 2016 (13.x) i nowszych Azure SQL DatabaseAzure SQL Managed Instancepunkt końcowy analizy SQL usługi Azure Synapse Analyticsw usłudze Microsoft FabricWarehouse w usłudze Microsoft Fabric

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

Transact-SQL konwencje składni

Składnia

JSON_MODIFY ( expression , path , newValue )

Argumenty

wyrażenia

Wyrażenie. Zazwyczaj nazwa zmiennej lub kolumny zawierającej tekst JSON.

JSON_MODIFY zwraca błąd, jeśli wyrażenie nie zawiera prawidłowego kodu JSON.

ścieżka

Wyrażenie ścieżki JSON określające właściwość do zaktualizowania.

ścieżka ma następującą składnię:

[append] [ lax | strict ] $.<json path>

  • dołączanie

    Opcjonalny modyfikator określający, że nowa wartość powinna zostać dołączona do tablicy przywoływanej przez <json path>.

  • lax

    Określa, że właściwość, do którego odwołuje się <json path>, nie musi istnieć. Jeśli właściwość nie jest obecna, JSON_MODIFY próbuje wstawić nową wartość na określonej ścieżce. Wstawianie może zakończyć się niepowodzeniem, jeśli nie można wstawić właściwości w ścieżce. Jeśli nie określisz lub ścisłej, jest trybem domyślnym.

  • ścisłe

    Określa, że właściwość przywoływane przez <json path> musi znajdować się w wyrażeniu JSON. Jeśli właściwość nie jest obecna, JSON_MODIFY zwraca błąd.

  • <json path>

    Określa ścieżkę właściwości do zaktualizowania. Aby uzyskać więcej informacji, zobacz wyrażenia ścieżki JSON (SQL Server).

    W programie SQL Server 2017 (14.x) i usłudze Azure SQL Database można podać zmienną jako wartość ścieżki .

    JSON_MODIFY zwraca błąd, jeśli format ścieżki jest nieprawidłowy.

newValue

Nowa wartość właściwości określonej przez ścieżkę .

Nowa wartość musi być varchar, nvarchar, char, tinyint, smallint, bigint, bitów, liczbowychrzeczywistychzmiennoprzecinkowych. Typ danych tekst nie jest obsługiwany.

W trybie lax JSON_MODIFY usuwa określony klucz, jeśli nowa wartość jest NULL.

JSON_MODIFY wyłącza wszystkie znaki specjalne w nowej wartości, jeśli typ wartości jest varchar lub nvarchar. Wartość tekstowa nie jest blokowana, jeśli poprawnie sformatowany kod JSON jest generowany przez FOR JSON, JSON_QUERYlub JSON_MODIFY.

Wartość zwracana

Zwraca zaktualizowaną wartość wyrażenia odpowiednio sformatowanego tekstu JSON.

Uwagi

Funkcja JSON_MODIFY umożliwia zaktualizowanie wartości istniejącej właściwości, wstawienie nowej pary key:value lub usunięcie klucza na podstawie kombinacji trybów i podanych wartości.

W poniższej tabeli porównaliśmy zachowanie JSON_MODIFY w trybie lax i w trybie ścisłym. Aby uzyskać więcej informacji na temat opcjonalnej specyfikacji trybu ścieżki (lax lub strict), zobacz wyrażenia ścieżki JSON (SQL Server).

Nowa wartość Ścieżka istnieje Tryb lax Tryb ścisły
NOT NULL Tak Zaktualizuj istniejącą wartość. Zaktualizuj istniejącą wartość.
NOT NULL Nie Spróbuj utworzyć nową parę klucz-wartość na określonej ścieżce.

Może to zakończyć się niepowodzeniem. Jeśli na przykład określisz ścieżkę $.user.setting.theme, JSON_MODIFY nie wstawia klucza theme, jeśli $.user lub obiekty $.user.settings nie istnieją, lub jeśli ustawienia są tablicą lub wartością skalarną.		 Błąd — INVALID_PROPERTY
NULL Tak Usuń istniejącą właściwość. Ustaw istniejącą wartość na null.
NULL Nie Brak akcji. Pierwszy argument jest zwracany w wyniku. Błąd — INVALID_PROPERTY

W trybie lax JSON_MODIFY próbuje utworzyć nową parę key:value, ale w niektórych przypadkach może się to nie powieść.

Funkcje JSON działają tak samo, czy dokument JSON jest przechowywany w varchar, nvarcharlub natywny json typu danych.

Przykłady

A. Podstawowe operacje

W poniższym przykładzie przedstawiono podstawowe operacje, które można wykonać za pomocą tekstu JSON.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update name
SET @info = JSON_MODIFY(@info, '$.name', 'Mike');
PRINT @info;

-- Insert surname
SET @info = JSON_MODIFY(@info, '$.surname', 'Smith');
PRINT @info;

-- Set name NULL
SET @info = JSON_MODIFY(@info, 'strict $.name', NULL);
PRINT @info;

-- Delete name
SET @info = JSON_MODIFY(@info, '$.name', NULL);
PRINT @info;

-- Add skill
SET @info = JSON_MODIFY(@info, 'append $.skills', 'Azure');
PRINT @info;

Oto zestaw wyników.

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

B. Wiele aktualizacji

Za pomocą JSON_MODIFYmożna zaktualizować tylko jedną właściwość. Jeśli musisz wykonać wiele aktualizacji, możesz użyć wielu wywołań JSON_MODIFY.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Multiple updates
SET @info = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info, '$.name', 'Mike'), '$.surname', 'Smith'), 'append $.skills', 'Azure');
PRINT @info;

Oto zestaw wyników.

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

C. Zmienianie nazwy klucza

W poniższym przykładzie pokazano, jak zmienić nazwę właściwości w tekście JSON za pomocą funkcji JSON_MODIFY. Najpierw możesz pobrać wartość istniejącej właściwości i wstawić ją jako nową parę key:value. Następnie możesz usunąć stary klucz, ustawiając wartość starej właściwości na NULL.

DECLARE @product NVARCHAR(100) = '{"price":49.99}';
PRINT @product;

-- Rename property
SET @product = JSON_MODIFY(JSON_MODIFY(@product, '$.Price', CAST(JSON_VALUE(@product, '$.price') AS NUMERIC(4, 2))), '$.price', NULL);
PRINT @product;

Oto zestaw wyników.

{
    "price": 49.99
} {
    "Price": 49.99
}

Jeśli nie rzutujesz nowej wartości na typ liczbowy, JSON_MODIFY traktuje ją jako tekst i otacza ją podwójnymi cudzysłowami.

D. Zwiększanie wartości

W poniższym przykładzie pokazano, jak zwiększać wartość właściwości w tekście JSON za pomocą funkcji JSON_MODIFY. Najpierw możesz pobrać wartość istniejącej właściwości i wstawić ją jako nową parę key:value. Następnie możesz usunąć stary klucz, ustawiając wartość starej właściwości na NULL.

DECLARE @stats NVARCHAR(100) = '{"click_count": 173}';
PRINT @stats;

-- Increment value
SET @stats = JSON_MODIFY(@stats, '$.click_count', CAST(JSON_VALUE(@stats, '$.click_count') AS INT) + 1);
PRINT @stats;

Oto zestaw wyników.

{
    "click_count": 173
} {
    "click_count": 174
}

E. Modyfikowanie obiektu JSON

JSON_MODIFY traktuje argument newValue jako zwykły tekst, nawet jeśli zawiera poprawnie sformatowany tekst JSON. W rezultacie dane wyjściowe JSON funkcji są otoczone podwójnymi cudzysłowami, a wszystkie znaki specjalne są ucieczki, jak pokazano w poniższym przykładzie.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', '["C#","T-SQL","Azure"]');
PRINT @info;

Oto zestaw wyników.

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}

Aby uniknąć automatycznego ucieczki, podaj newValue przy użyciu funkcji JSON_QUERY. JSON_MODIFY wie, że wartość zwracana przez JSON_QUERY jest prawidłowo sformatowana w formacie JSON, więc nie powoduje ucieczki wartości.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', JSON_QUERY('["C#","T-SQL","Azure"]'));
PRINT @info;

Oto zestaw wyników.

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": ["C#", "T-SQL", "Azure"]
}

F. Aktualizowanie kolumny JSON

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

UPDATE Employee
SET jsonCol = JSON_MODIFY(jsonCol, '$.info.address.town', 'London')
WHERE EmployeeID = 17;

Powiązana zawartość