Microsoft SQL Server
JSON im SQL Server
Suche…
Syntax
- JSON_VALUE (Ausdruck, Pfad) - Extrahieren Sie einen Skalarwert aus einem JSON-String.
- JSON_QUERY (Ausdruck [, Pfad]) - Extrahiert ein Objekt oder ein Array aus einem JSON-String.
- OPENJSON (jsonExpression [, path]) - Tabellenwertfunktion, die JSON-Text analysiert und Objekte und Eigenschaften in JSON als Zeilen und Spalten zurückgibt.
- ISJSON (Ausdruck) - Testet, ob eine Zeichenfolge gültige JSON enthält.
- JSON_MODIFY (Ausdruck, Pfad, NeuerWert) - Aktualisiert den Wert einer Eigenschaft in einem JSON-String und gibt den aktualisierten JSON-String zurück.
Parameter
Parameter | Einzelheiten |
---|---|
Ausdruck | In der Regel der Name einer Variablen oder einer Spalte, die JSON-Text enthält. |
Pfad | Ein JSON-Pfadausdruck, der die zu aktualisierende Eigenschaft angibt. path hat die folgende Syntax: [anfügen] [lax | strict] $. <Json-Pfad> |
JsonExpression | Ein Unicode-Zeichenausdruck, der den JSON-Text enthält. |
Bemerkungen
Die OPENJSON-Funktion ist nur unter Kompatibilitätsstufe 130 verfügbar. Wenn Ihre Datenbankkompatibilitätsstufe niedriger als 130 ist, kann der SQL Server die OPENJSON-Funktion nicht finden und ausführen. Derzeit sind alle Azure SQL-Datenbanken standardmäßig auf 120 festgelegt. Sie können den Kompatibilitätsgrad einer Datenbank mit dem folgenden Befehl ändern:
ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130
Formatieren Sie die Abfrageergebnisse als JSON mit FOR JSON
Eingabetabellendaten (Personentabelle)
Ich würde | Name | Alter |
---|---|---|
1 | John | 23 |
2 | Jane | 31 |
Abfrage
SELECT Id, Name, Age
FROM People
FOR JSON PATH
Ergebnis
[
{"Id":1,"Name":"John","Age":23},
{"Id":2,"Name":"Jane","Age":31}
]
Analysieren Sie den JSON-Text
JSON_VALUE- und JSON_QUERY- Funktionen analysieren JSON-Text und geben skalare Werte oder Objekte / Arrays im Pfad in JSON-Text zurück.
DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'
SELECT
JSON_VALUE(@json, '$.id') AS Id,
JSON_VALUE(@json, '$.user.name') AS Name,
JSON_QUERY(@json, '$.user') AS UserObject,
JSON_QUERY(@json, '$.skills') AS Skills,
JSON_VALUE(@json, '$.skills[0]') AS Skill0
Ergebnis
Ich würde | Name | UserObject | Kompetenzen | Fähigkeit0 |
---|---|---|---|---|
1 | John | {"name": "John"} | ["C #", "SQL"] | C # |
Verbinden Sie übergeordnete und untergeordnete JSON-Entitäten mit CROSS APPLY OPENJSON
Verbinden Sie übergeordnete Objekte mit ihren untergeordneten Entitäten. Zum Beispiel möchten wir eine relationale Tabelle für jede Person und ihre Hobbys
DECLARE @json nvarchar(1000) =
N'[
{
"id":1,
"user":{"name":"John"},
"hobbies":[
{"name": "Reading"},
{"name": "Surfing"}
]
},
{
"id":2,
"user":{"name":"Jane"},
"hobbies":[
{"name": "Programming"},
{"name": "Running"}
]
}
]'
Abfrage
SELECT
JSON_VALUE(person.value, '$.id') as Id,
JSON_VALUE(person.value, '$.user.name') as PersonName,
JSON_VALUE(hobbies.value, '$.name') as Hobby
FROM OPENJSON (@json) as person
CROSS APPLY OPENJSON(person.value, '$.hobbies') as hobbies
Alternativ kann diese Abfrage mit der WITH-Klausel geschrieben werden.
SELECT
Id, person.PersonName, Hobby
FROM OPENJSON (@json)
WITH(
Id int '$.id',
PersonName nvarchar(100) '$.user.name',
Hobbies nvarchar(max) '$.hobbies' AS JSON
) as person
CROSS APPLY OPENJSON(Hobbies)
WITH(
Hobby nvarchar(100) '$.name'
)
Ergebnis
Ich würde | Name der Person | Hobby |
---|---|---|
1 | John | lesen |
1 | John | Surfen |
2 | Jane | Programmierung |
2 | Jane | Laufen |
Indexieren Sie die JSON-Eigenschaften mithilfe von berechneten Spalten
Beim Speichern von JSON-Dokumenten in SQL Server müssen wir die Abfrageergebnisse nach Eigenschaften der JSON-Dokumente effizient filtern und sortieren können.
CREATE TABLE JsonTable
(
id int identity primary key,
jsonInfo nvarchar(max),
CONSTRAINT [Content should be formatted as JSON]
CHECK (ISJSON(jsonInfo)>0)
)
INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO
In Anbetracht der obigen Tabelle Wenn wir die Zeile mit dem Namen = 'Adam' finden möchten, führen wir die folgende Abfrage aus.
SELECT *
FROM JsonTable Where
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'
Dies erfordert jedoch, dass der SQL Server eine vollständige Tabelle ausführt, die bei einer großen Tabelle nicht effizient ist.
Um dies zu beschleunigen, möchten wir einen Index hinzufügen. Wir können jedoch nicht direkt auf Eigenschaften im JSON-Dokument verweisen. Die Lösung besteht darin, eine berechnete Spalte im JSON-Pfad $.Name
hinzuzufügen und dann einen Index für die berechnete Spalte hinzuzufügen.
ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')
CREATE INDEX idx_name
ON JsonTable(vName)
Wenn wir nun dieselbe Abfrage ausführen, verwendet der SQL Server anstelle einer vollständigen Tabellensuche einen Index, um den nicht gruppierten Index zu durchsuchen und die Zeilen zu finden, die die angegebenen Bedingungen erfüllen.
Anmerkung: Damit der SQL-Server den Index verwenden kann, müssen Sie die berechnete Spalte mit demselben Ausdruck erstellen, den Sie in Ihren Abfragen verwenden JSON_VALUE(jsonInfo, '$.Name')
- in diesem Beispiel JSON_VALUE(jsonInfo, '$.Name')
. Sie können jedoch auch den Namen verwenden der berechneten Spalte vName
Formatieren Sie eine Tabellenzeile mit FOR JSON als einzelnes JSON-Objekt
Die Option WITHOUT_ARRAY_WRAPPER in der FOR JSON- Klausel entfernt Array-Klammern aus der JSON-Ausgabe. Dies ist nützlich, wenn Sie eine einzelne Zeile in der Abfrage zurückgeben.
Hinweis: Diese Option erzeugt eine ungültige JSON-Ausgabe, wenn mehr als eine Zeile zurückgegeben wird.
Eingabetabellendaten (Personentabelle)
Ich würde | Name | Alter |
---|---|---|
1 | John | 23 |
2 | Jane | 31 |
Abfrage
SELECT Id, Name, Age
FROM People
WHERE Id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Ergebnis
{"Id":1,"Name":"John","Age":23}
Analysieren Sie JSON-Text mit der Funktion OPENJSON
Die Funktion OPENJSON analysiert den JSON-Text und gibt mehrere Ausgaben zurück. Werte, die zurückgegeben werden sollen, werden mithilfe der in der WITH-Klausel definierten Pfade angegeben. Wenn für eine Spalte kein Pfad angegeben ist, wird der Spaltenname als Pfad verwendet. Diese Funktion wandelt zurückgegebene Werte in die in der WITH-Klausel definierten SQL-Typen um. Die AS JSON-Option muss in der Spaltendefinition angegeben werden, wenn ein Objekt / Array zurückgegeben werden soll.
DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'
SELECT *
FROM OPENJSON (@json)
WITH(Id int '$.id',
Name nvarchar(100) '$.user.name',
UserObject nvarchar(max) '$.user' AS JSON,
Skills nvarchar(max) '$.skills' AS JSON,
Skill0 nvarchar(20) '$.skills[0]')
Ergebnis
Ich würde | Name | UserObject | Kompetenzen | Fähigkeit0 |
---|---|---|---|---|
1 | John | {"name": "John"} | ["C #", "SQL"] | C # |