Microsoft SQL Server
JSON im SQL Server

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

Bemerkungen

ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130

Formatieren Sie die Abfrageergebnisse als JSON mit FOR JSON

Eingabetabellendaten (Personentabelle)

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

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

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)

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