Microsoft SQL Server
OPENJSON
Suche…
Schlüssel abrufen: Wertepaare aus JSON-Text
Die Funktion OPENJSON analysiert den JSON-Text und gibt alle Schlüssel / Wert-Paare auf der ersten Ebene von JSON zurück:
declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}';
SELECT * FROM OPENJSON(@json);
Schlüssel | Wert | Art |
---|---|---|
Name | Joe | 1 |
Alter | 27 | 2 |
Kompetenzen | ["C #", "SQL"] | 4 |
Der Spaltentyp beschreibt den Wertetyp, dh null (0), String (1), number (2), boolean (3), Array (4) und Objekt (5).
Wandeln Sie das JSON-Array in einen Satz von Zeilen um
Die Funktion OPENJSON analysiert die Sammlung von JSON-Objekten und gibt Werte aus dem JSON-Text als Satz von Zeilen zurück.
declare @json nvarchar(4000) = N'[
{"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Quantity":1},
{"Number":"SO43661","Date":"2011-06-01T00:00:00","Customer":"Nokia","Price":24.99,"Quantity":3}
]'
SELECT *
FROM OPENJSON (@json)
WITH (
Number varchar(200),
Date datetime,
Customer varchar(200),
Quantity int
)
In der WITH-Klausel ist das Rückgabeschema der OPENJSON-Funktion angegeben. Schlüssel in den JSON-Objekten werden anhand von Spaltennamen abgerufen. Wenn ein Schlüssel in JSON nicht in der WITH-Klausel angegeben ist (z. B. Price in diesem Beispiel), wird er ignoriert. Werte werden automatisch in angegebene Typen konvertiert.
Nummer | Datum | Kunde | Menge |
---|---|---|---|
SO43659 | 2011-05-31T00: 00: 00 | MSFT | 1 |
SO43661 | 2011-06-01T00: 00: 00 | Nokia | 3 |
Wandeln Sie geschachtelte JSON-Felder in Zeilengruppen um
Die Funktion OPENJSON analysiert die Sammlung von JSON-Objekten und gibt Werte aus dem JSON-Text als Satz von Zeilen zurück. Wenn die Werte im Eingabeobjekt verschachtelt sind, können in jeder Spalte in WITH-Klausel zusätzliche Zuordnungsparameter angegeben werden:
declare @json nvarchar(4000) = N'[
{"data":{"num":"SO43659","date":"2011-05-31T00:00:00"},"info":{"customer":"MSFT","Price":59.99,"qty":1}},
{"data":{"number":"SO43661","date":"2011-06-01T00:00:00"},"info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'
SELECT *
FROM OPENJSON (@json)
WITH (
Number varchar(200) '$.data.num',
Date datetime '$.data.date',
Customer varchar(200) '$.info.customer',
Quantity int '$.info.qty',
)
In der WITH-Klausel ist das Rückgabeschema der OPENJSON-Funktion angegeben. Nachdem der Typ angegeben ist, wird der Pfad zu den JSON-Knoten angegeben, auf denen der zurückgegebene Wert gefunden werden soll. Schlüssel in den JSON-Objekten werden von diesen Pfaden abgerufen. Werte werden automatisch in angegebene Typen konvertiert.
Nummer | Datum | Kunde | Menge |
---|---|---|---|
SO43659 | 2011-05-31T00: 00: 00 | MSFT | 1 |
SO43661 | 2011-06-01T00: 00: 00 | Nokia | 3 |
Extrahieren von inneren JSON-Unterobjekten
OPENJSON kann Fragmente von JSON-Objekten im JSON-Text extrahieren. Setzen Sie in der Spaltendefinition, die auf ein JSON-Unterobjekt verweist, den Typ nvarchar (max) und die Option AS JSON:
declare @json nvarchar(4000) = N'[
{"Number":"SO43659","Date":"2011-05-31T00:00:00","info":{"customer":"MSFT","Price":59.99,"qty":1}},
{"Number":"SO43661","Date":"2011-06-01T00:00:00","info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'
SELECT *
FROM OPENJSON (@json)
WITH (
Number varchar(200),
Date datetime,
Info nvarchar(max) '$.info' AS JSON
)
Die Info-Spalte wird dem "Info" -Objekt zugeordnet. Ergebnisse werden sein:
Nummer | Datum | Info |
---|---|---|
SO43659 | 2011-05-31T00: 00: 00 | {"Kunde": "MSFT", "Preis": 59,99, "Anzahl": 1} |
SO43661 | 2011-06-01T00: 00: 00 | {"Kunde": "Nokia", "Preis": 24,99, "Anzahl": 3} |
Arbeiten mit verschachtelten JSON-Subarrays
JSON kann eine komplexe Struktur mit inneren Arrays aufweisen. In diesem Beispiel haben wir ein Array von Bestellungen mit verschachtelten Unterarrays von OrderItems.
declare @json nvarchar(4000) = N'[
{"Number":"SO43659","Date":"2011-05-31T00:00:00",
"Items":[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]},
{"Number":"SO43661","Date":"2011-06-01T00:00:00",
"Items":[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]}
]'
Wir können Eigenschaften der Stammebene mit OPENJSON analysieren, wodurch das Items-Array AS JSON-Fragment zurückgegeben wird. Dann können wir OPENJSON erneut auf das Items-Array anwenden und die innere JSON-Tabelle öffnen. Die Tabelle der ersten Ebene und die innere Tabelle werden wie beim JOIN zwischen Standardtabellen "verbunden":
SELECT *
FROM
OPENJSON (@json)
WITH ( Number varchar(200), Date datetime,
Items nvarchar(max) AS JSON )
CROSS APPLY
OPENJSON (Items)
WITH ( Price float, Quantity int)
Ergebnisse:
Nummer | Datum | Artikel | Preis | Menge |
---|---|---|---|---|
SO43659 | 2011-05-31 00: 00: 00.000 | [{"Preis": 11,99, "Anzahl": 1}, {"Preis": 12,99, "Anzahl": 5}] | 11,99 | 1 |
SO43659 | 2011-05-31 00: 00: 00.000 | [{"Preis": 11,99, "Anzahl": 1}, {"Preis": 12,99, "Anzahl": 5}] | 12,99 | 5 |
SO43661 | 2011-06-01 00: 00: 00.000 | [{"Preis": 21.99, "Anzahl": 3}, {"Preis": 22.99, "Menge": 2}, {"Preis": 23.99, "Menge": 2}] | 21,99 | 3 |
SO43661 | 2011-06-01 00: 00: 00.000 | [{"Preis": 21.99, "Anzahl": 3}, {"Preis": 22.99, "Menge": 2}, {"Preis": 23.99, "Menge": 2}] | 22,99 | 2 |
SO43661 | 2011-06-01 00: 00: 00.000 | [{"Preis": 21.99, "Anzahl": 3}, {"Preis": 22.99, "Menge": 2}, {"Preis": 23.99, "Menge": 2}] | 23,99 | 2 |