Microsoft SQL Server
Abfragen mit JSON-Daten

Werte aus JSON in Abfrage verwenden

Mit der Funktion JSON_VALUE können Sie Daten aus dem JSON-Text in dem als zweiten Argument angegebenen Pfad abrufen und diesen Wert in einem beliebigen Teil der Auswahlabfrage verwenden:

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'

JSON-Werte in Berichten verwenden

Sobald JSON-Werte aus JSON-Text extrahiert wurden, können Sie sie in einem beliebigen Teil der Abfrage verwenden. Sie können einen Bericht über JSON-Daten mit Gruppierungsaggregaten usw. erstellen:

select JSON_VALUE(Data, '$.Type') as type,
        AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type') 
having JSON_VALUE(Data, '$.Type') is not null

Ungültigen JSON-Text aus Abfrageergebnissen herausfiltern

Wenn ein JSON-Text möglicherweise nicht richtig formatiert ist, können Sie diese Einträge mithilfe der ISJSON-Funktion aus der Abfrage entfernen.

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0

Aktualisieren Sie den Wert in der JSON-Spalte

Die JSON_MODIFY-Funktion kann verwendet werden, um den Wert in einem bestimmten Pfad zu aktualisieren. Sie können diese Funktion verwenden, um den ursprünglichen Wert der JSON-Zelle in der UPDATE-Anweisung zu ändern:

update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;

Die JSON_MODIFY-Funktion aktualisiert oder erstellt einen Preisschlüssel (falls nicht vorhanden). Wenn der neue Wert NULL ist, wird der Schlüssel entfernt. Die JSON_MODIFY-Funktion behandelt den neuen Wert als Zeichenfolge (Escape-Sonderzeichen, mit doppelten Anführungszeichen umschließen, um die richtige JSON-Zeichenfolge zu erstellen). Wenn Ihr neuer Wert ein JSON-Fragment ist, sollten Sie ihn mit der JSON_QUERY-Funktion einschließen:

update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;

Die JSON_QUERY-Funktion ohne zweiten Parameter verhält sich wie eine Umwandlung in JSON. Da das Ergebnis von JSON_QUERY ein gültiges JSON-Fragment (Objekt oder Array) ist, wird JSON_MODIFY diesen Wert nicht ändern, wenn die Eingabe-JSON geändert wird.

Neuen Wert an JSON-Array anhängen

Die JSON_MODIFY-Funktion kann verwendet werden, um einen neuen Wert an ein Array in JSON anzuhängen:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;

Ein neuer Wert wird am Ende des Arrays angehängt oder ein neues Array mit dem Wert ["sales"] wird erstellt. Die JSON_MODIFY-Funktion behandelt den neuen Wert als Zeichenfolge (Escape-Sonderzeichen, mit doppelten Anführungszeichen umschließen, um die richtige JSON-Zeichenfolge zu erstellen). Wenn Ihr neuer Wert ein JSON-Fragment ist, sollten Sie ihn mit der JSON_QUERY-Funktion einschließen:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;

Die JSON_QUERY-Funktion ohne zweiten Parameter verhält sich wie eine Umwandlung in JSON. Da das Ergebnis von JSON_QUERY ein gültiges JSON-Fragment (Objekt oder Array) ist, wird JSON_MODIFY diesen Wert nicht ändern, wenn die Eingabe-JSON geändert wird.

JOIN-Tabelle mit innerer JSON-Kollektion

Wenn Sie eine "untergeordnete Tabelle" haben, die als JSON-Auflistung formatiert und in Reihe als JSON-Spalte gespeichert ist, können Sie diese Auflistung entpacken, in eine Tabelle umwandeln und sie mit der übergeordneten Zeile verbinden. Anstelle des Standardoperators JOIN sollten Sie CROSS APPLY verwenden. In diesem Beispiel werden Produktteile als Sammlung von JSON-Objekten in der Datenspalte formatiert und in dieser gespeichert:

select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
    CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))

Das Ergebnis der Abfrage entspricht dem Join zwischen Produkt- und Teiletabellen.

Zeilen finden, die Werte im JSON-Array enthalten

In diesem Beispiel kann das Tags-Array verschiedene Schlüsselwörter wie ["promo", "sales"] enthalten. Daher können wir dieses Array öffnen und Werte filtern:

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSON öffnet die innere Sammlung von Tags und gibt sie als Tabelle zurück. Dann können wir die Ergebnisse nach einem Wert in der Tabelle filtern.