JSON_MODIFY (Transact-SQL)

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics

Aktualisiert den Wert einer Eigenschaft in einer JSON-Zeichenfolge und gibt die aktualisierte JSON-Zeichenfolge zurück.

Transact-SQL-Syntaxkonventionen

Syntax

JSON_MODIFY ( expression , path , newValue )

Argumente

expression

Ein Ausdruck. In der Regel der Name einer Variablen oder einer Spalte, die JSON-Text enthält.

JSON_MODIFY gibt einen Fehler zurück, wenn expression keinen gültigen JSON-Text enthält.

path

Ein JSON-Pfadausdruck, der die zu aktualisierende Eigenschaft angibt.

path verfügt über die folgende Syntax:

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

• append

  Optionaler Modifizierer, der angibt, dass der neue Wert an das Array angefügt werden sollte, auf das <json path> verweist.

• lax

  Gibt an, dass die eigenschaft, auf <json path> die verwiesen wird, nicht vorhanden sein muss. Wenn die Eigenschaft nicht vorhanden ist, versucht, JSON_MODIFY den neuen Wert in den angegebenen Pfad einzufügen. Dies kann fehlschlagen, wenn die Eigenschaft nicht in den Pfad eingefügt werden kann. Wenn Sie weder lax noch strict angeben, ist der Standardmodus lax.

• strict

  Gibt an, dass die Eigenschaft, auf die <json path> verweist, im JSON-Ausdruck enthalten sein muss. Wenn die Eigenschaft nicht vorhanden ist, JSON_MODIFY wird ein Fehler zurückgegeben.

• <json path>

  Gibt den Pfad für die zu aktualisierende Eigenschaft an. Weitere Informationen finden Sie unter JSON-Pfadausdrücke (SQL Server).

  In SQL Server 2017 (14.x) und Azure SQL-Datenbank können Sie eine Variable als Wert von path bereitstellen.

  JSON_MODIFY gibt einen Fehler zurück, wenn das Format von path ungültig ist.

newValue

Der neue Wert für die von path angegebene Eigenschaft.

Der neue Wert muss varchar, nvarchar oder text sein.

Im Lax-Modus löscht JSON_MODIFY den angegebenen Schlüssel, wenn der neue Wert NULL ist.

JSON_MODIFY escapes all special characters in the new value if the type of the value is varchar or nvarchar. Ein Textwert wird nicht mit Escapezeichen versehen, wenn es ordnungsgemäß formatierter JSON-Code ist, der von FOR JSON, oder JSON_QUERYJSON_MODIFY.

Rückgabewert

Gibt den aktualisierten Wert von expression als ordnungsgemäß formatierten JSON-Text zurück.

Hinweise

Mit der JSON_MODIFY Funktion können Sie entweder den Wert einer vorhandenen Eigenschaft aktualisieren, ein neues Schlüssel-Wert-Paar einfügen oder einen Schlüssel basierend auf einer Kombination von Modi und bereitgestellten Werten löschen.

Die folgende Tabelle vergleicht das Verhalten von JSON_MODIFY im Lax-Modus und im Strict-Modus. Weitere Informationen zu den optionalen Pfadmodusangaben („lax“ oder „strict“) finden Sie unter JSON-Pfadausdrücke (SQL Server).

Im Lax-Modus versucht JSON_MODIFY, ein neues Schlüssel-Wert-Paar zu erstellen, aber in einigen Fällen schlägt dies möglicherweise fehl.

JSON-Funktionen funktionieren gleich, unabhängig davon, ob das JSON-Dokument in varchar, nvarchar oder dem nativen json-Datentyp gespeichert ist.

Beispiele

A. Basisvorgänge

Das folgende Beispiel zeigt die grundlegenden Vorgänge, die mit JSON-Text ausgeführt werden können.

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;

Hier sehen Sie das Ergebnis.

{
    "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. Mehrere Updates

Mit JSON_MODIFYdieser Eigenschaft können Sie nur eine Eigenschaft aktualisieren. Wenn Sie mehrere Updates ausführen müssen, können Sie mehrere JSON_MODIFY Anrufe verwenden.

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;

Hier sehen Sie das Ergebnis.

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

C. Umbenennen eines Schlüssels

Das folgende Beispiel zeigt, wie Sie eine Eigenschaft in JSON-Text mit der JSON_MODIFY Funktion umbenennen. Zunächst können Sie den Wert einer vorhandenen Eigenschaft annehmen. Fügen Sie ihn als neues Schlüssel-Wert-Paar ein. Anschließend können Sie den alten Schlüssel löschen, indem Sie den Wert der alten Eigenschaft auf NULLfestlegen.

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;

Hier sehen Sie das Ergebnis.

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

Wenn der neue Wert nicht in einen numerischen Typ umgewandelt wird, behandelt JSON_MODIFY ihn als Text und umgibt ihn mit doppelten Anführungszeichen.

D: Erhöhen eines Werts

Im folgenden Beispiel wird die Vorgehensweise beim Erhöhen eines Eigenschaftswerts in JSON-Text mit der JSON_MODIFY-Funktion gezeigt. Zunächst können Sie den Wert der vorhandenen Eigenschaft annehmen. Fügen Sie ihn als neues Schlüssel-Wert-Paar ein. Anschließend können Sie den alten Schlüssel löschen, indem Sie den Wert der alten Eigenschaft auf NULLfestlegen.

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;

Hier sehen Sie das Ergebnis.

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

E. Ändern eines JSON-Objekts

JSON_MODIFY behandelt das newValue-Argument als Nur-Text, auch wenn ordnungsgemäß formatierter JSON-Text enthalten ist. Daher ist die JSON-Ausgabe der Funktion von doppelten Anführungszeichen eingeschlossen. Alle Sonderzeichen werden mit Escapezeichen versehen, wie im folgenden Beispiel gezeigt.

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;

Hier sehen Sie das Ergebnis.

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

Geben Sie zum Vermeiden der automatischen Escapezeichen newValue mithilfe der JSON_QUERY-Funktion an. JSON_MODIFY weiß, dass der von JSON_QUERY zurückgegebene Wert ordnungsgemäß im JSON-Format angegeben ist, weshalb der Wert nicht mit einem Escapezeichen versehen wird.

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;

Hier sehen Sie das Ergebnis.

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

F. Aktualisieren einer JSON-Spalte

Im folgenden Beispiel wird der Wert einer Eigenschaft in einer Tabellenspalte, die JSON enthält, aktualisiert.

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

Zugehöriger Inhalt

• JSON-Pfadausdrücke (SQL Server)
• JSON-Daten (SQL Server)