Überprüfen, Abfragen und Ändern von JSON-Daten mit integrierten Funktionen (SQL Server)
Gilt für: 
SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance
Die integrierte Unterstützung für JSON umfasst die folgenden integrierten Funktionen, die in diesem Artikel kurz beschrieben werden.
- ISJSON testet, ob eine Zeichenfolge gültiges JSON enthält.
- JSON_VALUE extrahiert einen skalaren Wert aus einer JSON-Zeichenfolge.
- JSON_QUERY extrahiert ein Objekt oder ein Array aus einer JSON-Zeichenfolge.
- JSON_MODIFY aktualisiert den Wert einer Eigenschaft in einer JSON-Zeichenfolge und gibt die aktualisierte JSON-Zeichenfolge zurück.
Informationen zu allen JSON-Funktionen finden Sie unter JSON-Funktionen.
Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.
JSON-Text für die Beispiele auf dieser Seite
In den Beispielen auf dieser Seite wird der JSON-Text verwendet, der dem im folgenden Beispiel gezeigten Inhalt ähnelt:
{
"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
}
Dieses JSON-Dokument, das geschachtelte komplexe Elemente enthält, wird in der folgenden Beispieltabelle gespeichert:
CREATE TABLE Families (
id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
[doc] NVARCHAR(MAX)
);
JSON-Funktionen funktionieren gleich, unabhängig davon, ob das JSON-Dokument in varchar, nvarchar oder dem nativen json-Datentyp gespeichert ist.
Überprüfen von JSON-Text mithilfe der ISJSON-Funktion
Die ISJSON-Funktion testet, ob eine Zeichenfolge gültiges JSON enthält.
Im folgenden Beispiel werden die Spalten zurückgegeben, in denen die JSON-Spalte einen gültigen JSON-Text enthält. Ohne explizite JSON-Beschränkung können Sie jeden beliebigen Text in der nvarchar-Spalte eingeben:
SELECT *
FROM Families
WHERE ISJSON(doc) > 0;
Weitere Informationen finden Sie unter ISJSON.
Extrahieren eines Wertes aus JSON-Text mithilfe der JSON_VALUE-Funktion
Die JSON_VALUE-Funktion extrahiert einen skalaren Wert aus einer JSON-Zeichenfolge. Die folgende Abfrage gibt die Dokumente zurück, in denen das JSON-Feld id mit dem Wert DesaiFamily übereinstimmt, sortiert nach den JSON-Feldern city und state:
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
Die Ergebnisse dieser Abfrage sind in der folgenden Tabelle aufgeführt:
| Name | City | Verwaltungsbezirk |
|---|---|---|
DesaiFamily |
NY |
Manhattan |
Weitere Informationen finden Sie unter JSON_VALUE.
Extrahieren eines Objekts oder eines Arrays aus JSON-Text mithilfe der JSON_QUERY-Funktion
Die JSON_QUERY-Funktion extrahiert ein Objekt oder ein Array aus einer JSON-Zeichenfolge. Im folgenden Beispiel wird gezeigt, wie ein JSON-Fragment in den Abfrageergebnissen zurückgegeben wird.
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';
Die Ergebnisse dieser Abfrage sind in der folgenden Tabelle aufgeführt:
| Adresse | Übergeordnete Elemente (Parents) | Übergeordnetes Element 0 (Parent0) |
|---|---|---|
{ "state": "NY", "county": "Manhattan", "city": "NY" } |
[ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ] |
{ "familyName": "Desai", "givenName": "Prashanth" } |
Weitere Informationen finden Sie unter JSON_QUERY.
Analysieren geschachtelter JSON-Sammlungen
Mit der OPENJSON-Funktion können Sie das JSON-Subarray in das Rowset umwandeln und dann mit dem übergeordneten Element verknüpfen. Als Beispiel können Sie alle Familiendokumente zurückgeben und sie mit ihren children-Objekten „verknüpfen“, die als inneres JSON-Array gespeichert sind:
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
Die Ergebnisse dieser Abfrage sind in der folgenden Tabelle aufgeführt:
| Name | Ort | givenName | Klasse |
|---|---|---|---|
DesaiFamily |
NY |
Jesse |
1 |
DesaiFamily |
NY |
Lisa |
8 |
Das Ergebnis sind zwei Zeilen, da eine übergeordnete Zeile mit zwei untergeordneten Zeilen verbunden ist, die durch das Analysieren von zwei Elementen des untergeordneten Subarrays erzeugt werden. Die OPENJSON-Funktion analysiert children-Fragmente aus der Spalte doc und gibt grade und givenName von jedem Element als Zeilen zurück. Dieses Rowset kann mit dem übergeordneten Dokument verknüpft werden.
Abfragen geschachtelter hierarchischer JSON-Subarrays
Sie können mehrere CROSS APPLY OPENJSON-Aufrufe anwenden, um geschachtelte JSON-Strukturen abzufragen. Das in diesem Beispiel verwendete JSON-Dokument verfügt über ein geschachteltes Array namens children, bei dem jedes untergeordnete Element ein geschachteltes Array von pets aufweist. Mit der folgenden Abfrage werden die untergeordneten Elemente aus den einzelnen Dokumenten analysiert, jedes Arrayobjekt als Zeile zurückgegeben und dann das pets-Array analysiert:
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;
Der erste OPENJSON-Befehl gibt ein Fragment des children-Arrays mithilfe der AS JSON-Klausel zurück. Dieses Arrayfragment wird der zweiten OPENJSON-Funktion bereitgestellt, die givenName zurückgibt, firstName jedes untergeordneten Elements, und das Arrays von pets. Das Array von pets wird der dritten OPENJSON-Funktion bereitgestellt, die das givenName-Element des Haustiers zurückgibt.
Die Ergebnisse dieser Abfrage sind in der folgenden Tabelle aufgeführt:
| familyName | childGivenName | petName |
|---|---|---|
Desai |
Jesse |
Goofy |
Desai |
Jesse |
Shadow |
Desai |
Lisa |
NULL |
Das Stammdokument wird mit zwei children-Zeilen verknüpft, die beim ersten OPENJSON(children)-Aufruf von zwei Zeilen (oder Tupeln) zurückgegeben werden. Anschließend wird jede Zeile mit den neuen Zeilen verknüpft, die von OPENJSON(pets) mithilfe des OUTER APPLY-Operators generiert werden. Weil Jesse zwei Haustiere hat, ist (Desai, Jesse) mit zwei Zeilen verknüpft, die für Goofy und Shadow generiert werden. Lisa hat keine Haustiere, sodass für dieses Tupel keine Zeilen von OPENJSON(pets) zurückgegeben werden. Da jedoch OUTER APPLY verwendet wird, wird NULL in der Spalte zurückgegeben. Wenn CROSS APPLY anstelle von OUTER APPLY eingesetzt wird, wird Lisa nicht im Ergebnis zurückgegeben, da keine Zeilen für Haustiere vorhanden sind, die mit diesem Tupel verknüpft werden könnten.
Vergleichen von JSON_VALUE und JSON_QUERY
Der Hauptunterschied zwischen JSON_VALUE und JSON_QUERY ist, dass JSON_VALUE einen skalaren Wert zurückgibt, wogegen JSON_QUERY ein Objekt oder Array zurückgibt.
Betrachten Sie das folgende Beispiel eines JSON-Texts.
{
"a": "[1,2]",
"b": [1, 2],
"c": "hi"
}
In diesem Beispiel-JSON-Text sind die Datenelemente „a“ und „c“ Zeichenfolgenwerte, während Datenelement „b“ ein Array ist. Die folgenden Ergebnisse werden von JSON_VALUE und JSON_QUERY zurückgegeben:
| Pfad | JSON_VALUE-Rückgaben |
JSON_QUERY-Rückgaben |
|---|---|---|
$ |
NULL oder Fehler |
{ "a": "[1,2]", "b": [1, 2], "c": "hi" } |
$.a |
[1,2] |
NULL oder Fehler |
$.b |
NULL oder Fehler |
[1,2] |
$.b[0] |
1 |
NULL oder Fehler |
$.c |
hi |
NULL oder Fehler |
Testen von JSON_VALUE und JSON_QUERY mit der AdventureWorks-Beispieldatenbank
Testen Sie die integrierten Funktionen, die in diesem Artikel beschrieben werden, indem Sie die folgenden Beispiele mit der AdventureWorks2022-Beispieldatenbank ausführen. Weitere Informationen zum Hinzufügen von JSON-Daten für Testzwecke durch Ausführen eines Skripts finden Sie unter Testen des in die JSON-Unterstützung integrierten Laufwerks.
Im folgenden Beispiel enthält die Info-Spalte in der Tabelle SalesOrder_json den JSON-Text.
Beispiel 1 – Gib sowohl Standardspalten als auch JSON-Daten zurück
Die folgende Abfrage gibt sowohl die relationalen Standardspalten sowie Werte aus einer JSON-Spalte zurück.
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;
Beispiel 2 – Aggregiere und filtere JSON-Werte
Die folgende Abfrage aggregiert Teilergebnisse nach Kundennamen (im JSON-Format gespeichert), und Status (gespeichert in einer normalen Spalte). Sie filtert dann die Ergebnisse nach Stadt (im JSON-Format gespeichert) und Bestelldatum (gespeichert in einer normalen Spalte).
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;
Aktualisieren von Eigenschaftswerten in JSON-Text mithilfe der JSON_MODIFY-Funktion
Die JSON_MODIFY-Funktion aktualisiert den Wert einer Eigenschaft in einer JSON-Zeichenfolge, und gibt die aktualisierte JSON-Zeichenfolge zurück.
Im folgenden Beispiel wird der Wert einer JSON-Eigenschaft in einer Variable aktualisiert, die JSON enthält.
SET @info = JSON_MODIFY(@jsonInfo, '$.info.address[0].town', 'London');
Weitere Informationen finden Sie unter JSON_MODIFY.