Der MS SQL Server 2016 lernt JSON

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

Erzeugen der Datenstruktur

 

Tabelle Buecher mit Testdaten füllen

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

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

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

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

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

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

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

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

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.

Diesen Beitrag mit anderen teilen