Teilen über

Speichern von JSON-Dokumenten in SQL Server oder SQL-Datenbank

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-DatenbankAzure SQL Managed Instance

Die SQL-Datenbank-Engine verfügt über native JSON-Funktionen, mit denen Sie JSON-Dokumente durch die standardmäßige SQL-Sprache analysieren können. Sie können JSON-Dokumente in SQL Server oder SQL-Datenbank speichern und JSON-Daten wie in einer NoSQL-Datenbank abfragen. Dieser Artikel beschreibt die Optionen zum Speichern von JSON-Dokumenten.

JSON-Speicherformat

Die erste Entscheidung zum Speicherentwurf betrifft das Speichern von JSON-Dokumenten in den Tabellen. Es stehen zwei Optionen zur Verfügung:

  • LOB-Speicher: JSON-Dokumente können unverändert in Spalten mit dem Datentyp json oder nvarchar gespeichert werden. Dies ist die beste Möglichkeit für schnelles Laden von Daten und Erfassen, da die Ladegeschwindigkeit dem Laden von Zeichenfolgenspalten entspricht. Dieser Ansatz kann zu einer zusätzlichen Leistungseinbuße bei der Abfrage-/Analysezeit führen, wenn die Indizierung von JSON-Werten nicht durchgeführt wird, da die unformatierten JSON-Dokumente analysiert werden müssen, während die Abfragen ausgeführt werden.

  • Relationaler Speicher: JSON-Dokumente können analysiert werden, während sie mithilfe von OPENJSON-, JSON_VALUE- oder JSON_QUERY-Funktionen in die Tabelle eingefügt werden. Fragmente aus den eingegebenen JSON-Dokumenten können in den Spalten gespeichert werden, die JSON-Unterelemente mit den Datentypen json oder nvarchar enthalten. Diese Vorgehensweise erhöht die Ladezeit, da die JSON-Analyse beim Laden erfolgt. Die Abfrageleistung entspricht jedoch der Leistung bei klassischen Abfragen relationaler Daten.

  • JSON ist derzeit kein integrierter Datentyp in SQL Server.

  • Derzeit ist der JSON-Datentyp in Azure SQL-Datenbank verfügbar.

Klassische Tabellen

Die einfachste Möglichkeit zum Speichern von JSON-Dokumenten in SQL Server oder Azure SQL-Datenbank stellt die Erstellung einer zweispaltigen Tabelle dar, die die ID und den Inhalt des Dokuments enthält. Zum Beispiel:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

Oder, soweit unterstützt:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

Diese Struktur entspricht den Sammlungen, die Sie in klassischen Dokumentdatenbanken finden. Der Primärschlüssel _id ist ein automatisch inkrementierter Wert, der einen eindeutigen Bezeichner für jedes Dokument bereitstellt und schnelle Suchvorgänge ermöglicht. Diese Struktur ist eine gute Wahl für die klassischen NoSQL-Szenarios, in denen Sie ein Dokument mit der ID abrufen oder ein gespeichertes Dokument mit einer bestimmten ID aktualisieren möchten.

  • Verwenden Sie soweit verfügbar den nativen json-Datentyp, um JSON-Dokumente zu speichern.
  • Mit dem Datentyp nvarchar(max) können Sie JSON-Dokumente mit einer Größe von bis zu 2 GB speichern. Wenn Sie jedoch sicher sind, dass Ihre JSON-Dokumente nicht größer als 8 KB sind, wird empfohlen, aus Leistungsgründen nvarchar(4000) anstelle von nvarchar(max) zu verwenden.

Bei der im vorherigen Beispiel erstellten Beispieltabelle wird davon ausgegangen, dass gültige JSON-Dokumente in der Spalte log gespeichert sind. Wenn Sie sichergehen wollen, dass ein gültiges JSON-Objekt in der Spalte log gespeichert ist, können Sie für die Spalte eine CHECK-Einschränkung hinzufügen. Zum Beispiel:

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

Jedes Mal, wenn ein Dokument in die Tabelle eingefügt oder in der Tabelle aktualisiert wird, überprüft diese Einschränkung, ob das JSON-Dokument korrekt formatiert ist. Ohne diese Einschränkung ist die Tabelle für Einfügungen optimiert, da jedes JSON-Dokument ohne weitere Verarbeitung direkt zur Spalte hinzugefügt wird.

Wenn Sie die JSON-Dokumente in der Tabelle speichern, können Sie zum Abfragen der Dokumente die Standardsprache Transact-SQL verwenden. Zum Beispiel:

SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE([log], '$.severity')
 HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC

Die Möglichkeit, jede T-SQL-Funktion und Abfrageklausel zum Abfragen von JSON-Dokumenten verwenden zu können, ist besonders nützlich. SQL Server und SQL-Datenbank führen in den Abfragen, die Sie zur Analyse von JSON-Dokumenten verwenden können, keine Einschränkungen ein. Sie können mit der Funktion JSON_VALUE Werte aus einem JSON-Dokument extrahieren und wie jeden anderen Wert in der Abfrage verwenden.

Diese Fähigkeit, umfassende T-SQL-Abfragesyntax zu verwenden, ist der entscheidende Unterschied zwischen SQL Server und SQL-Datenbank und klassischen NoSQL-Datenbanken – bei Transact-SQL sind aller Wahrscheinlichkeit nach alle Funktionen vorhanden, die Sie zur Verarbeitung von JSON-Daten benötigen.

Indizes

Wenn Sie feststellen, dass Ihre Abfragen häufig Dokumente nach bestimmten Eigenschaften durchsuchen (z.B. nach einer severity-Eigenschaft in einem JSON-Dokument), können Sie einen klassischen nicht gruppierten Index für die Eigenschaft hinzufügen, um die Abfragen zu beschleunigen.

Sie können eine berechnete Spalte erstellen, die JSON-Werte von den JSON-Spalten für den angegebenen Pfad (d.h. für den Pfad $.severity) verfügbar macht, und einen Standardindex für diese berechnete Spalte erstellen. Zum Beispiel:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

Bei der in diesem Beispiel verwendeten berechneten Spalte handelt es sich um eine nicht persistierte oder virtuelle Spalte, die den Speicherplatz der Tabelle nicht erweitert. Diese wird vom Index ix_severity verwendet, um wie im folgenden Beispiel die Leistung von Abfragen zu verbessern:

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

Ein wichtiges Merkmal dieses Index ist, dass er sich sortieren lässt. Wenn Ihre ursprüngliche nvarchar-Spalte über eine COLLATION-Eigenschaft verfügt (z.B. Groß-/Kleinschreibung oder Japanisch), wird der Index nach den Sprach- oder Groß-/Kleinschreibungsregeln der nvarchar-Spalte organisiert. Die Unterstützung dieser Sortierung kann sich für die Entwicklung von Anwendungen für globale Märkte, die benutzerdefinierte Sprachregeln bei der Verarbeitung von JSON-Dokumenten erfordern, als wichtiges Feature erweisen.

Große Formate für Tabellen und Columnstore-Indizes

Wenn Sie mit einer großen Anzahl von JSON-Dokumenten in Ihrer Sammlung rechnen, empfehlen wir Ihnen, einen gruppierten Columnstore-Index für die Sammlung hinzuzufügen, wie im folgenden Beispiel gezeigt wird:

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    [_id] bigint default(next value for WebSite.LogID),
    [log] json,
    INDEX cci CLUSTERED COLUMNSTORE
);

Ein gruppierter Columnstore-Index ermöglicht eine hohe (bis zu 25-fache) Datenkomprimierung, wodurch Sie Ihren Speicherplatzbedarf deutlich reduzieren, die Speicherkosten senken und die E/A-Leistung Ihrer Workloads erhöhen können. Außerdem sind gruppierte Columnstore-Indizes für Tabellenscans und Analysen für Ihre JSON-Dokumente optimiert, sodass dieser Indextyp die beste Option für Protokollanalysen darstellen kann.

Im vorhergehende Beispiel wird ein Sequenzobjekt verwendet, um der Spalte _id Werte zuzuweisen. Sowohl Sequenzen als auch Identitäten sind gültige Optionen für die ID-Spalte.

Häufig geänderte Dokumente und speicheroptimierte Tabellen

Wenn Sie mit einer großen Anzahl von Aktualisierungs-, Einfüge- und Löschvorgängen in Ihren Sammlungen rechnen, können Sie Ihre JSON-Dokumente in speicheroptimierten Tabellen speichern. Speicheroptimierte JSON-Sammlungen verwalten die Daten immer im Arbeitsspeicher, sodass kein Speicher-E/A-Overhead erzeugt wird. Darüber hinaus sind speicheroptimierte JSON-Sammlungen komplett sperrenfrei, d. h., andere Vorgänge werden durch Aktionen für Dokumente nicht blockiert.

Um eine klassische Sammlung in eine speicheroptimierte Sammlung zu konvertieren, müssen Sie lediglich die Option WITH (MEMORY_OPTIMIZED=ON) nach der Tabellendefinition angeben, wie im folgenden Beispiel gezeigt wird. Dann haben Sie eine speicheroptimierte Version der JSON-Sammlung erstellt.

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json
) WITH (MEMORY_OPTIMIZED=ON)

Eine speicheroptimierte Tabelle ist die beste Option für häufig geänderte Dokumente. Wenn Sie speicheroptimierte Tabellen in Erwägung ziehen, sollten Sie auch die Leistung berücksichtigen. Verwenden Sie für die JSON-Dokumente in Ihren speicheroptimierten Sammlungen möglichst nvarchar(4000) anstelle von nvarchar(max), da die Leistung hierdurch deutlich verbessert werden kann. Der json-Datentyp wird für speicheroptimierte Tabellen nicht unterstützt.

Wie bei klassischen Tabellen können Sie durch berechnete Spalten Indizes für die Felder, die Sie in speicheroptimierten Tabellen verfügbar machen, hinzufügen. Zum Beispiel:

CREATE TABLE WebSite.Logs (

  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json,

  [severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
  INDEX ix_severity (severity)

) WITH (MEMORY_OPTIMIZED=ON)

Um die Leistung zu maximieren, sollten Sie den JSON-Wert in den kleinstmöglichen Typ umwandeln, der zum Speichern des Eigenschaftswerts verwendet werden kann. Im vorherigen Beispiel wurde tinyint verwendet.

Sie können auch SQL-Abfragen, die JSON-Dokumente aktualisieren, in gespeicherte Prozeduren einbinden, um von den Vorteilen der nativen Kompilierung zu profitieren. Zum Beispiel:

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET [log] = JSON_MODIFY([log], @Property, @Value)
    WHERE _id = @Id;

END

Diese nativ kompilierte Prozedur erstellt anhand der Abfrage einen DLL-Code, der die Abfrage ausführt. Mit einer nativ kompilierten Prozedur können Sie schneller Daten abfragen und aktualisieren.

Zusammenfassung

Durch native JSON-Funktionen in SQL Server und SQL-Datenbank können JSON-Dokumente wie in NoSQL-Datenbanken verarbeitet werden. Datenbanken – ganz gleich, ob relationale oder NoSQL-Datenbanken – weisen Vor- und Nachteile hinsichtlich der Verarbeitung von JSON-Daten auf. Der größte Vorteil bei der Speicherung von JSON-Dokumenten in SQL Server oder SQL-Datenbank besteht in der vollständigen Unterstützung für SQL-Sprachen. Sie können mithilfe der umfangreichen Transact-SQL-Sprache Daten verarbeiten und eine Vielzahl von Speicheroptionen konfigurieren, von Columnstore-Indizes für umfangreiche Komprimierungen und schnelle Analysen bis hin zu speicheroptimierten Tabellen für eine Verarbeitung ohne Sperren. Gleichzeitig profitieren Sie von ausgereiften Sicherheits- und Internationalisierungsfeatures, die Sie in Ihrem NoSQL-Szenario einfach wiederverwenden können. Die in diesem Artikel beschriebenen Gründe sind überzeugende Argumente dafür, die Speicherung von JSON-Dokumenten in SQL Server oder SQL-Datenbank in Erwägung zu ziehen.

Weitere Informationen zu JSON in SQL Server und Azure SQL-Datenbank

Eine visuelle Einführung in die JSON-Unterstützung, die in SQL Server und Azure SQL-Datenbank integriert ist, finden Sie in den folgenden Videos:

Zugehöriger Inhalt