Freigeben über

JSON_MODIFY (Transact-SQL)

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-DatenbankAzure SQL Managed InstanceAzure 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).

Neuer Wert Pfad ist vorhanden Lax-Modus Strict-Modus
NOT NULL Ja Vorhandenen Wert aktualisieren. Vorhandenen Wert aktualisieren.
NOT NULL No Versuchen Sie, ein neues Schlüsselwertpaar auf dem angegebenen Pfad zu erstellen.

Dies kann fehlschlagen. Wenn Sie z. B. den Pfad $.user.setting.themeangeben, JSON_MODIFY wird der Schlüssel theme nicht eingefügt, wenn die $.user Objekte $.user.settings nicht vorhanden sind, oder wenn Einstellungen ein Array oder ein Skalarwert sind.		 Fehler: INVALID_PROPERTY
NULL Ja Löscht die vorhandene Eigenschaft. Legt den vorhandenen Wert auf NULL fest.
NULL No Keine Aktion. Das erste Argument wird als Ergebnis zurückgegeben. Fehler: INVALID_PROPERTY

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, 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 ist das Resultset.

{
    "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 ist das Resultset.

{
    "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 ist das Resultset.

{
    "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 ist das Resultset.

{
    "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 ist das Resultset.

{
    "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 ist das Resultset.

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