Der MS SQL Server 2016 lernt JSON
Die Verbreitung von JSON als Datenformat ist im Web enorm. Als schlankes Datenformat ist es beim Transfer von Daten zwischen Server und Client bzw. Server und Server das Mittel der Wahl. Dementsprechend unterstützen die meisten Programmiersprachen JSON problemlos. Aber wie sieht es bei der Speicherung von JSON im SQL Server aus. Bis zur Version 2016 wurden JSON-Daten einfach in NVARCHAR-Spalten gespeichert und bei Bedarf vollständig ausgelesen.
Mit dem SQL Server 2016 ändert sich das. Mit dieser Version ist es möglich JSON-Zeichenketten direkt in T-SQL zu verarbeiten und nur die benötigten Daten auszugeben.
Zunächst soll aber die Frage geklärt werden, was sich bei der Speicherung von JSON- Zeichenketten im SQL Server geändert hat. Im Prinzip nichts. Als Datentyp wird immer noch NVARCHAR angegeben. Zusätzlich kann aber ein CONTRAINT auf die Spalte gelegt der sicherstellt, dass in diese Spalte eine gültige JSON-Zeichenkette eingefügt wird.
Zur Veranschaulichung dieses Artikels soll das aus anderen Artikeln bekannte „Bucharchiv“-Szenario verwendet werden. Dazu wird eine Tabelle „Buecher“ erstellt, die mit zwei Datensätzen gefüllt wird.
Erzeugen der Datenstruktur
Tabelle Buecher mit Testdaten füllen
JSON-Zeichenketten im SQL Server Datenschema
Als Erweiterung für diesen Artikel soll eine neue Spalte „JSONFormatted“ hinzugefügt werden. Diese soll mit einem CONSTRAINT verbunden werden, der die nachfolgende Meldung ausgibt, wenn die einzufügende Zeichenkette nicht dem JSON Format entspricht.
Hinzufügen der JSON-Spalte
Der CONSTRAINT verwendet in der CHECK-Funktion bereits die erste neue JSON-Methode die mit dem SQL Server 2016 eingefügt wurde. „ISJSON“ prüft ob eine Zeichenkette dem JSON Format entspricht und gibt als Ergebnis eins oder null zurück. Wird nun beispielsweise versucht eine leere Zeichenkette einzufügen, bricht die Aktion ab und liefert die folgende Meldung.
Einfügen einer Nicht-JSON-Zeichenkette
Für die folgenden Beispiele soll die neue Spalte mit folgenden Daten befüllt werden.
Befüllen der neuen JSON-SPalte
Verarbeitend und aufbereiten von JSON-Zeichenketten in SQL Abfragen
Zum teilweisen Auslesen der JSON-Zeichenkette bzw. zur Vorverarbeitung innerhalb einer SQL Query wurden die Funktionen „JSON_VALUE“ und „JSON_QUERY“ eingeführt.
Mit Hilfe der Funktion „JSON_VALUE“ kann ein einzelner Wert aus einer JSON-Zeichenkette ausgelesen werden. Die einzelnen Ebenen des JSON werden über die Punkt-Notation angesprochen. Das „$“ bildet hierbei den Einstiegspunkt. Beim Auslesen von Array-Werten muss zusätzlich zum Element der entsprechende Index, auf den zugegriffen werden soll, angegeben werden.
Verwendung von JSON_VALUE
Im Gegensatz zur Funktion „JSON_VALUE“ gibt die „JSON_QUERY“ Funktion keinen einzelnen Wert aus, sondern den Inhalt des angegebenen Elementes. Das Ergebnis kann wiederum ein JSON-Objekt sein oder der Inhalt eines Arrays.
Verwendung von JSON_QUERY
Ergänzt werden diese beiden Funktionen durch die Funktion „OPENJSON“. Diese öffnet eine JSON-Struktur in der wie in den vorangegangenen Funktionen navigiert werden kann. Der Unterschied ist, dass die Funktion das Ergebnis nicht als Einzelwert oder JSON-Struktur ausgibt, sondern als Key/Value-Paare. Bei der Ausgabe eines Arrays wird als „Key“ der Index des Elementes verwendet.
Verwendung von OPENJSON
In Kombination mit dem Befehl „WITH“ können für die Ausgabe Spalten, wie in einer Tabelle, inkl. unter Angabe des Datentyps definiert werden.
Verwendung von OPENJSON und WITH
Der Verwendung der neuen JSON Funktionen schränkt sich aber nicht nur aus den Ausgabebereich. Mit Hilfe der „JSON_VALUE“ Funktion können die anzuzeigenden Datensätze einer Abfrage über eine „WHERE“ Bedingung gefiltert werden.
Gefilterte SQL Abfragen mit JSON_VALUE
Insgesamt bieten die drei vorgestellten Funktionen alles notwendige, um vorhandene JSON-Zeichenketten für eine SQL Abfrage aufzubereiten und entsprechend auszugeben. Mussten dazu in den vorangegangenen Versionen des SQL Servers noch komplizierte gespeicherte Prozeduren oder Views erstellt werden, kann dies seit dieser Version mit einem einfachen Funktionsaufruf erledigt werden. Probieren Sie es aus. Weitere Informationen zu diesem Thema finden sie hier.
