---

April 2017

Band 32, Nummer 4

Cutting Edge: Abfragen von JSON-Daten in SQL Server 2016

Von Dino Esposito | April 2017

Das Übertragen von Daten zwischen unabhängigen und autonomen Systemen ist zurzeit die Hauptaufgabe der meisten Software, und JSON ist die ubiquitäre Sprache hinter dieser Datenübertragung. Das Akronym „JSON“ steht für „JavaScript Object Notation“. JSON bietet eine textbasierte Möglichkeit, den Zustand eines Objekts bereitzustellen, damit es auf einfache Weise serialisiert und von einem System an das nächste übertragen werden kann, insbesondere in heterogenen Systemen.

JSON ist zu etwas geworden, an dem XML letztlich gescheitert ist: JSON die Verkehrssprache des Webs. Ich persönlich glaube nicht daran, dass JSON leichter zu lesen ist als XML. Andererseits ist JSON ein Textformat, das kompakter und einfacher als XML ist und von Menschen bearbeitet werden kann. Computer aus einer langen Liste von Software- und Hardwareplattformen können die Sprache schnell analysieren und verstehen.

Eine JSON-Zeichenfolge ist eine Nur-Text-Zeichenfolge. In jeder Version eines RDBMS (Relational Database Management System, Managementsystem für relationale Datenbanken) einschließlich SQL Server kann eine Zeichenfolge unabhängig von ihrem Inhaltslayout gespeichert werden. SQL Server 2016 ist jedoch die erste Version der Microsoft-Datenbank, mit der Sie vorhandene Tabellendaten als JSON lesen, Tabellendaten als JSON speichern und (viel wichtiger) Abfragen in JSON-Zeichenfolgen so ausführen können, als handele es sich bei dem JSON-Inhalt tatsächlich um eine Sammlung einzelner Spalten.

Einen strukturierten und umfassenden Überblick über die JSON-Funktionen in SQL Server 2016 finden Sie in der MSDN-Dokumentation unter bit.ly/2llab1n. Außerdem steht eine ausgezeichnete Kurzfassung von JSON in SQL Server 2016 im Simple Talk-Artikel unter bit.ly/26rprwv zur Verfügung. Der Artikel bietet eine geschäftsorientiertere Sicht auf JSON in SQL Server 2016 und stellt im Allgemeinen eine szenariobasierte Perspektive der Verwendung von JSON-Daten in einer relationalen Persistenzschicht zur Verfügung.

JSON-Daten in der Persistenzschicht

Zwei Verben bilden den Schlüssel für das Verständnis des Zwecks von JSON: übertragen und serialisieren. JSON ist das Format, in dem Sie den Zustand einer Softwareentität bereitstellen, damit sie über Prozessräume mit der Sicherheit übertragen werden kann, dass sie von beiden Seiten gut verstanden wird. Das klingt gut, aber dies ist eine Kolumne über JSON in SQL Server und daher auch in der Persistenzschicht. Fangen wir also mit der Grundfrage an: Wann würden Sie Daten in SQL Server als JSON speichern?

Eine relationale Datenbanktabelle wird für eine feste Anzahl von Spalten definiert, und jede Spalte besitzt ihren eigenen Datentyp, z. B. Zeichenfolgen variabler oder fester Länge, Datumsangaben, Zahlen, boolesche Werte usw. JSON ist kein nativer Datentyp. Eine SQL Server-Spalte, die JSON-Daten enthält, ist aus der Datenbankperspektive eine Nur-Zeichenfolgen-Spalte. Sie können JSON-Daten wie eine reguläre Zeichenfolge in eine Tabellenspalte schreiben, und dies kann in jeder beliebigen Version von SQL Server sowie in beliebigen anderen RDBMS erfolgen.

Woher stammen die JSON-Zeichenfolgen, die Sie letztendlich in einer Datenbank speichern? Zwei Hauptszenarien sind denkbar: Zum einen können diese Zeichenfolgen aus einem Webdienst oder aus einer anderen Art von externem Endpunkt stammen, der Daten überträgt (z. B. von einem verbundenen Gerät oder einem Sensor). Zum anderen können JSON-Daten ein praktisches Verfahren zum Gruppieren in Beziehung stehender Informationen sein, damit diese als ein Datenelement vorliegen. Dies geschieht in der Regel, wenn Sie mit halbstrukturierten Daten arbeiten, z. B. mit Daten, die ein Geschäftsereignis darstellen, das in einem Event Sourcing-Szenario oder (viel einfacher) in einem Geschäftskontext gespeichert werden soll, der inhärent ereignisgesteuert ist (z. B. Echtzeitsysteme für Domänen wie Finanzwesen, Handel, Bewertungen, Überwachung, industrielle Automatisierung und Steuerung usw.). In allen diesen Fällen kann ihr Speicher in eine strukturierte Form normalisiert werden, indem in Beziehung stehende Informationen mit variabler Länge und variablem Format in ein einzelnes Datenelement serialisiert werden, das in die Zeichenfolgenspalte einer relationalen Tabelle passt.

Wie bereits erwähnt, kann der JSON-Inhalt, den Sie persistent speichern möchten, aus einer externen Quelle stammen oder über Serialisierung aus Instanzen von C#-Objekten generiert werden:

foreach (var c in countries)
{
  // Serialize the C# object to JSON
  var json = JsonConvert.SerializeObject(c);
  // Save content to the database
  record.JsonColumn = json;
}

Sie können auch Entity Framework (EF) zum Speichern von JSON-Daten in einer Spalte einer Datenbanktabelle verwenden.

SQL Server 2016 geht noch einen Schritt weiter und ermöglicht Ihnen das Transformieren von JSON-Daten in Tabellenzeilen. Diese Möglichkeit kann viel Arbeit und viele CPU-Zyklen Ihres Codes sparen, weil Sie nun den JSON-Rohtext mithilfe von Push an die Datenbank übertragen können, ohne ihn zuerst in C#-Objekte im Anwendungscode zu analysieren und dann über EF- oder direkte ADO.NET-Aufrufe zu analysieren. Der Schlüssel zum Erreichen dieses Ziels ist die neue OPENJSON-Funktion:

declare @country nvarchar(max) = '{
  "id" : 101,
  "name": "United States",
  "continent": "North America"
}';
  INSERT INTO Countries
    SELECT * FROM OPENJSON(@country)
    WITH (id int,
      name nvarchar(100),
      continent nvarchar(100))

Sie können diese Funktion zum Einfügen oder Aktualisieren regulärer Tabellenzeilen aus JSON-Nur-Text verwenden. Mit der WITH-Klausel können Sie JSON-Eigenschaften vorhandenen Tabellenspalten zuordnen.

Das Event Sourcing-Szenario

In meiner Kolumne aus Dezember 2016 habe ich Event Sourcing als ein zunehmend verwendetes Muster zum Speichern des historischen Zustands der Anwendung beschrieben (msdn.com/magazine/mt790196). Anstatt den letzten bekannten fehlerfreien Zustand zu speichern, wird bei Event Sourcing jedes einzelne Geschäftsereignis gespeichert, das den Zustand ändert, und der aktuelle Zustand durch Wiedergeben der letzten Ereignisse erneut erstellt.

Der wesentliche Aspekt einer Event Sourcing-Implementierung besteht darin, wie effektiv die vergangenen Ereignisse gespeichert und abgerufen werden können. Jedes Ereignis ist anders und verwendet abhängig vom Typ und den verfügbaren Informationen ggf. ein anderes Schema. Gleichzeitig ist die Verwendung eines eigenen (relationalen) Speichers für jeden Ereignistyp problematisch, weil Ereignisse asynchron auftreten und sich möglicherweise auf verschiedene Entitäten und verschiedene Segmente des Zustands auswirken. Wenn Sie diese in verschiedenen Tabellen speichern, kann das erneute Erstellen des Zustands aufgrund tabellenübergreifender JOIN-Vorgänge aufwändig sein. Daher ist das Speichern von Ereignissen als Objekte die empfehlenswerteste Option, und NoSQL-Speicher erledigen diese Aufgabe sehr effizient. Ist es möglich, Event Sourcing stattdessen mit einer relationalen Datenbank auszuführen?

Das Speichern des Ereignisses als JSON stellt eine Option dar, die für jede Version von SQL Server möglich ist, aber das effektive Lesen von JSON bei einer großen Anzahl von Ereignissen im Speicher ist unter Umständen untragbar. Durch die nativen JSON-Features in SQL Server 2016 ändert sich die Sachlage jedoch, und die Verwendung von SQL Server in einem Event Sourcing-Szenario wird zu einer realistischen Vorstellung. Wie aber würde JSON aus einer Datenbanktabelle abgefragt?

Abfragen von Daten aus JSON-Inhalt

Angenommen, Sie verwalten mindestens eine Spalte mit JSON-Daten in einer kanonischen relationalen Tabelle. Spalten mit primitiven Daten und Spalten mit JSON-Daten sind also parallel vorhanden. Wenn die neuen Funktionen von SQL Server 2016 nicht verwendet werden, werden die JSON-Spalten als Nur-Text-Felder behandelt und können nur mit Zeichenfolgen- und Textanweisungen von T-SQL wie LIKE, SUBSTRING und TRIM abgefragt werden. Für die Demo habe ich eine Spalte namens „Countries“ (mit wenigen tabellarischen Spalten) und eine weitere Spalte namens „Serialized“ erstellt, die den gesamten Rest des als JSON serialisierten Datensatzes enthält. Abbildung 1 zeigt dies.

Abbildung 1: Die Beispieldatenbank „Countries“ mit einer JSON-Spalte

Das in die Beispieltabelle serialisierte JSON-Objekt sieht wie folgt aus:

{
  "CountryCode":"AD",
  "CountryName":"Andorra",
  "CurrencyCode":"EUR",
  "Population":"84000",
  "Capital":"Andorra la Vella",
  "ContinentName":"Europe",
  "Continent":"EU",
  "AreaInSqKm":"468.0",
  "Languages":"ca",
  "GeonameId":"3041565",
  "Cargo":null

Die folgende T-SQL-Abfrage zeigt, wie nur die Länder ausgewählt werden, deren Einwohnerzahl größer als 100 Millionen ist. Die Abfrage mischt reguläre Tabellenspalten und JSON-Eigenschaften:

SELECT CountryCode,
  CountryName,
  JSON_VALUE(Serialized, '$.Population') AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized, '$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

Die Funktion JSON_VALUE nimmt den Namen einer JSON-Spalte an (oder eine lokale Variable, die auf eine JSON-Zeichenfolge festgelegt ist) und extrahiert den Skalarwert, der auf den angegebenen Pfad folgt. Wie in Abbildung 2 gezeigt wird, bezieht sich das $-Symbol auf den Stamm des serialisierten JSON-Objekts.

Abbildung 2: Ergebnisse einer JSON-Abfrage

Da die JSON-Spalte als eine Nur-NVARCHAR-Spalte konfiguriert ist, können Sie die ISJSON-Funktion zum Überprüfen verwenden, ob der Inhalt der Spalte echtes JSON ist. Die Funktion gibt einen positiven Wert zurück, wenn es sich bei dem Inhalt um JSON handelt.

JSON_VALUE gibt unabhängig von der ausgewählten Eigenschaft immer eine Zeichenfolge von bis zu 4.000 Bytes zurück. Wenn Sie einen längeren Rückgabewert erwarten, sollten Sie stattdessen OPENJSON verwenden. Bei jeder Größe sollte Sie jedoch ggf. einen CAST-Vorgang in Betracht ziehen, um einen Wert des richtigen Typs abzurufen. Wenden wir uns noch einmal dem vorherigen Beispiel zu. Angenommen, Sie möchten die Einwohnerzahl eines Landes mit Kommas formatiert abrufen. (Im Allgemeinen ist dies keine gute Idee, weil das Formatieren von Daten in der Präsentationsschicht Ihrem Code viel mehr Flexibilität verleiht.) Die SQL FORMAT-Funktion erwartet als Eingabe eine Zahl und gibt einen Fehler aus, wenn Sie den direkten JSON-Wert übergeben. Damit dies funktioniert, müssen Sie auf einen expliziten CAST-Vorgang zurückgreifen:

SELECT CountryCode,
  CountryName,
  FORMAT(CAST(
    JSON_VALUE(Serialized, '$.Population') AS int), 'N0')
    AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized,'$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

JSON_VALUE kann nur einen einzelnen Skalarwert zurückgeben. Wenn Sie über ein Array eines geschachtelten Objekts verfügen, das extrahiert werden soll, müssen Sie auf die JSON_QUERY-Funktion zurückgreifen.

Wie effektiv ist die Abfrage von JSON-Daten? Führen wir einige Tests aus.

Indizieren von JSON-Inhalt in SQL Server 2016

Es scheint offensichtlich zu sein, aber das Abfragen der gesamten JSON-Zeichenfolge aus der Datenbank und das anschließende Analysieren der Zeichenfolge im Arbeitsspeicher mithilfe einer dedizierten Bibliothek wie Newtonsoft JSON funktioniert zwar immer, ist aber nicht unter allen Umständen eine effektive Vorgehensweise. Die Effektivität hängt größtenteils von der Anzahl der Datensätze in der Datenbank und der Zeitdauer ab, die wirklich benötigt wird, um die benötigten Daten im gewünschten Format abzurufen. Für eine Abfrage, die Ihre Anwendung gelegentlich ausführt, ist die In-Memory-Verarbeitung von JSON-Daten möglicherweise eine Option. Im Allgemeinen führt jedoch die Abfrage über JSON zugeordnete Funktionen und die interne Analyse der Ergebnisse durch SQL zu etwas schnellerem Code. Der Unterschied wird noch größer, wenn Sie einen Index für JSON-Daten hinzufügen.

Sie sollten den Index jedoch nicht für die JSON-Spalte erstellen, weil der JSON-Wert als eine einzelne Zeichenfolge indiziert würde. Sie werden wohl kaum die gesamte JSON-Zeichenfolge oder eine Teilmenge davon abfragen. Es ist realistischer, dass Sie den Wert einer bestimmten Eigenschaft im serialisierten JSON-Objekt abfragen. Eine effektivere Vorgehensweise besteht im Erstellen mindestens einer berechneten Spalte basierend auf dem Wert von mindestens einer JSON-Eigenschaft und dem anschließenden Indizieren dieser Spalten. Hier ein Beispiel in T-SQL:

-- Add a computed column
ALTER TABLE dbo.Countries
ADD JsonPopulation
AS JSON_VALUE(Serialized, '$.Population')
-- Create an index
CREATE INDEX IX_Countries_JsonPopulation
ON dbo.Countries(JsonPopulation)

Auch hier sollten Sie sich klarmachen, dass NVARCHAR von JSON_VALUE zurückgegeben wird. Wenn Sie keinen CAST-Vorgang hinzufügen, wird der Index für Text erstellt.

Interessanterweise ist die JSON-Analyse schneller als die Deserialisierung einiger Sondertypen wie XML und räumlicher Typen. Weitere Informationen finden Sie unter bit.ly/2kthrrC. Zusammengefasst lässt sich sagen, dass die JSON-Analyse wenigstens besser als das Abrufen von Eigenschaften anderer Typen ist.

JSON und EF

Allgemein sei angemerkt, dass die JSON-Unterstützung in SQL Server 2016 hauptsächlich über die T-SQL-Syntax bereitgestellt wird, weil Tools zurzeit nur recht eingeschränkt verfügbar sind. Insbesondere EF stellt zurzeit keine Möglichkeiten zum Abfragen von JSON-Daten bereit. Ausnahmen sind die SqlQuery-Methode in EF6 und FromSql in EF Core. Dies bedeutet jedoch nicht, dass Sie komplexe Eigenschaften von C#-Klassen (z. B. Arrays) nicht in JSON-Spalten serialisieren können. Ein ausgezeichnetes Tutorial zu EF Core finden Sie unter bit.ly/2kVEsam.

Zusammenfassung

Mit SQL Server 2016 werden einige native JSON-Funktionen eingeführt, damit gespeicherte JSON-Daten als ein kanonisches Rowset effektiver abgefragt werden können. Dies geschieht in den meisten Fällen, wenn die JSON-Daten die serialisierte Version von halbstrukturierten Aggregatdaten darstellen. Indizes, die aus berechneten Werten erstellt werden, die diesen Wert von mindestens einer JSON-Eigenschaft widerspiegeln, wirken sich definitiv positiv auf die Leistung aus.

JSON-Daten werden als Nur-Text gespeichert und nicht als spezieller Typ (z. B. XML und Spatial) betrachtet. Genau dies ermöglicht Ihnen jedoch die unmittelbare Verwendung von JSON-Spalten in beliebigen SQL Server-Objekten. Dies gilt nicht für andere komplexe Typen wie XML, CLR und Spatial, die sich noch auf der Warteliste befinden.

In dieser Kolumne habe ich den Schwerpunkt auf das JSON-zu-Rowset-Szenario gelegt. SQL Server 2016 unterstützt jedoch auch das Rowset-zu-JSON-Abfrageszenario vollständig, wenn Sie eine reguläre T-SQL-Abfrage schreiben und dann die Ergebnisse JSON-Objekten über die FOR JSON-Klausel zuordnen. Weitere Informationen zu diesem Feature finden Sie unter bit.ly/2fTKly7.

---

Dino Espositoist Autor von „Microsoft .NET: Architecting Applications for the Enterprise“ (Microsoft Press, 2014) und „Modern Web Applications with ASP.NET“ (Microsoft Press, 2016). Esposito ist Technical Evangelist für die .NET- und Android-Plattformen bei JetBrains und spricht häufig auf Branchenveranstaltungen weltweit. Auf software2cents.wordpress.com und auf Twitter unter @despos lässt er uns wissen, welche Softwarevision er verfolgt.

Unser Dank gilt dem folgenden technischen Experten bei Microsoft für die Durchsicht dieses Artikels: Jovan Popovic