Update vom 16.10.2022: Dieser Artikel bezieht sich auf EF Core 5. Ab EF Core 7 ist das Speichern von Json-Dokumenten im SQL-Server im Standard-Lieferumfang dabei. Im .Net Blog unter [1] wird beschrieben, wie das Feature funktioniert. In diesem Artikel möchte ich mich mit einem doch recht speziellen Thema beschäftigen, welches mir neulich bei einem Projekt begegnet ist. Die Anforderung, Json-Daten in einem SQL-Server zu speichern, klingt zunächst sehr ungewöhnlich bis falsch. SQL-Server ist sicher nicht die erste Wahl dazu, da die Datenbank auf strukturierte Daten ausgelegt ist, welche sich auf Tabellen und Beziehungen zwischen den Tabellen abbilden lassen. Unstrukturierte Json-Dokumente passen hier nicht wirklich ins Konzept, dokumentenorientierte Datenbanken sind dagegen auf solche Daten spezialisiert. Wie kommt man nun auf die Idee, SQL-Server dafür zu verwenden? Ganz einfach, es geht u. A. um den Betrieb. Man muss es sich schon überlegen, ob man wegen einer einzelnen neuen Anforderung direkt eine komplett neue Technologie betreiben möchte. Der SQL-Server und das Wissen darüber dagegen steht im Projekt und im Betrieb schon zur Verfügung. Nach einer kurzen Recherche stellte sich für mich zudem heraus, dass es durchaus nicht einmal so ungewöhnlich ist, SQL-Server zum Speichern von Json-Dokumenten zu verwenden. Es gilt lediglich, einige Punkte zu beachten.
Inhaltsverzeichnis
Lösungen und Artikel zum Thema
In den Weiten des Internets finden sich bereits einige Lösungen und Artikel, welche sich mit genau diesem Thema beschäftigen. Einzig bezogen auf Entity Framework Core findet sich etwas weniger. Eine gute Lösung ohne Entity Framework Core etwa ist das Projekt Nevermore von OctopusDeploy [2]. Insbesondere das Wiki des Projekts bietet umfassende Informationen darüber, wie man Nevermore verwendet und wie es unter der Haube funktioniert.
Daneben gibt es eine größere Zahl an Artikeln, welche sich allgemein mit den Speichern von Json-Daten in SQL-Server beschäftigen. Ein gutes Beispiel dafür ist “Storing JSON in SQL Server” von Jovan Popovic [3]. Er beschreibt kurz und knapp alles Wesentliche rund um dieses Thema. In der Dokumentation von Microsoft unter [4] befinden sich darüber hinaus weitere Informationen, etwa zu Funktionen im SQL-Server, über die mit Json-Dokumenten umgegangen werden kann. So kann etwa mit ISJSON geprüft werden, ob in einem Feld gültige Json-Daten liegen. Auch Queries oder Manipulationen gegen die Json-Daten sind möglich.
An der Stelle noch eine andere interessante Information. Microsoft möchte die Unterstützung für Json-Spalten ab Version 7 direkt in Entity Framework Core integrieren [5].
Json-Daten in einer VARCHAR(MAX) oder VARBINARY(MAX) Spalte
Der erste Punkt ist im Prinzip auch der Einfachste. Um Json-Dokumente in einer SQL-Server Datenbank abzulegen, legt man dafür idealerwiese eigene Spalten vom Typ VARCHAR(MAX) oder VARBINARY(MAX) an. Ersteres gilt für unkomprimierte Json-Daten, letzteres für komprimierte Json-Daten. Mit der Kompression möchte ich mich etwas später im Artikel beschäftigen, darum konzentrieren wir uns zunächst auf das VARCHAR(MAX).
Das MAX hat keinen direkten Bezug zu Json. Wenn man davon ausgehen kann, dass die Json-Dokumente eine bestimmte Größe nicht überschreiten, kann die Spalte entsprechend kürzer angelegt werden. Json-Dokumente können in dieser Spalte als String gespeichert werden. Es ist keine weitere Information für den SQL-Server notwendig, dass es sich um ein Json-Dokument handelt. Für Entity Framework Core handelt es sich im Model somit um eine Eigenschaft vom Typ String. Das Model würde dazu etwa wie im folgenden Codeausschnitt aussehen.
public class ModelWithJsonData1
{
[StringLength(30)]
public string ID { get; private set; }
public string JsonData { get; private set; }
private ModelWithJsonData()
{
//...
}
}
Auffällig an diesen Codeausschnitt ist, dass das Model trotzdem über ein Feld ID verfügt. Felder wie der Primärschlüssel oder solche, die innerhalb von Queries verwendet werden, sollten als eigene Felder in der Tabelle angelegt sein. Der Hintergrund dafür ist schlicht der, dass mit den üblichen Techniken des Entity Framework Core bzw. des SQL-Servers performant darauf zugegriffen werden kann. Auch ein Index kann wie gewohnt auf solche Felder gesetzt werden. Der Nachteil ist aber, dass dadurch Redundanz entstehen kann – und zwar, wenn eine Information sowohl im Json-Dokument, also auch in der Tabelle in einer eigenen Spalte steht. Der Entwickler muss sich selbst darum kümmern, dass in solchen Fällen die Felder in der Tabelle und im Json-Dokument synchron gehalten werden. Obig verwiesene Lösung Nevermore nimmt dem Entwickler diese Verantwortlichkeit ab, basiert allerdings nicht auf Entity Framework Core.
Das Serialisieren und Deserialisieren der Json-Daten wird durch Entity Framework Core nicht abgenommen. Ein starkes Hilfsmittel dafür ist die Klasse JsonConvert von Json.Net [5]. Selbstverständlich können auch andere Serializer verwendet werden.
Kompression der Json-Daten im SQL-Server
Je nach Größe und Anzahl der Json-Dokumente kann es Sinn machen, die Json-Dokumente im SQL-Server komprimiert abzulegen. Randolph West weißt in seinem Artikel [7] ebenso auf dieses Thema hin, da die Größe der Daten am SQL-Server nicht unterschätzt werden sollte. In meinem Szenario etwa war Komprimierung notwendig, da es sich um mehrere Millionen Json-Dokumente gehandelt hat, von denen ein einzelnes zwischen ca. 10.000 und 50.000 Zeichen lang sein kann. Die Lösung bezogen auf Entity Framework Core ist zunächst relativ einfach. Es genügt, wie im nachfolgenden Codeausschnitt zu sehen anstelle eines String ein byte[] im Model zu verwenden. Aus dem Typ byte[] wird automatisch ein VARBINARY(MAX).
public class ModelWithJsonData1
{
[StringLength(30)]
public string ID { get; private set; }
public byte[] JsonData { get; private set; }
private ModelWithJsonData()
{
//...
}
}
Die Komprimierung erfolgt nun während der Serialisierung und Deserialisierung. Nachfolgender Codeausschnitt zeigt eine einfache Hilfsklasse, welche genau das erledigen soll. Zur Komprimierung wird nicht ohne Grund ein GZipStream verwendet. Dieser ist mit den Funktionen COMPRESS und DECOMPRESS am SQL-Server kompatibel. Somit kann nach wie vor auch am SQL-Server auf die Json-Daten zugegriffen werden, sie müssen lediglich vorher per DECOMPRESS dekomprimiert werden.
public class JsonRootSerializer
{
public static byte[] SerializeToJson(JsonRoot rootObj, bool reducedPropertySize)
{
return Zip(JsonConvert.SerializeObject(rootObj, Formatting.None));
}
public static JsonRoot DeserializeFromValue(byte[] value, bool reducedPropertySize)
{
var serializer = new JsonSerializer();
using var jsonReader = new JsonTextReader(UnZip(value));
return serializer.Deserialize<JsonRoot>(jsonReader);
}
private static byte[] Zip(string value)
{
// Transform string into byte[]
var byteArray = Encoding.UTF8.GetBytes(value);
// Prepare for compress
using var ms = new MemoryStream((int)(byteArray.Length * 0.6));
using var sw = new GZipStream(ms, CompressionLevel.Optimal);
// Compress
sw.Write(byteArray, 0, byteArray.Length);
sw.Close();
// Transform byte[] zip data to string
return ms.ToArray();
}
private static StreamReader UnZip(byte[] value)
{
// Prepare for decompress
var ms = new MemoryStream(value);
var sr = new GZipStream(ms, CompressionMode.Decompress);
return new StreamReader(sr);
}
}
Beispiel
Ein funktionierendes Beispiel habe ich unter [8] auf Github hochgeladen. Es handelt sich um ein Experiment, welches die beiden Varianten Komprimiert und Unkomprimiert gegenüberstellen soll.
Fazit
Zu Beginn des Artikels habe ich geschrieben, Json in SQL-Server klingt zunächst sehr ungewöhnlich bis falsch. Nachdem ich mich mit dem Thema tiefer beschäftigt habe, hat sich meine Meinung dazu (leicht) verändert. Zwar ist der Weg über dokumentenorientierte Datenbanken sicher besser, so schlecht wie anfangs gedacht ist der Weg über SQL-Server allerdings nicht. Die Umsetzung einer Lösung ist vergleichsweise einfach, zudem bietet der SQL-Server darüber hinaus mehrere Funktionen, wie mit den Json-Daten interagiert werden kann. Mittels der Kompression schließlich existiert ebenfalls ein guter Weg, mit der Größe der Daten umzugehen. Der größte Nachteil, der übrig bleibt, ist die mögliche Redundanz zwischen Feldern in der Tabelle und Feldern im Json-Dokument.
Downloads
- Beispiel-Quellcode
https://www.rolandk.de/files/2022/HappyCoding.JsonInSqlServer.zip
Verweise
- Beschreibung des neues .Net 7 Features “JSON Columns”
https://devblogs.microsoft.com/dotnet/announcing-ef7-release-candidate-2/ - Repository des Projekts Nevermore von OctopusDeploy
https://github.com/OctopusDeploy/Nevermore - Allgemeine Infos zum Speichern von Json-Daten im SQL-Server
https://techcommunity.microsoft.com/t5/sql-server-blog/storing-json-in-sql-server/ba-p/384576 - Dokumentation zum Speichern von Json-Daten im SQL-Server
https://docs.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-ver15 - Geplante Features in Entity Framework Core 7
https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/plan - Dokumentation zur Klasse JsonConvert von Newtonsoft
https://www.newtonsoft.com/json/help/html/t_newtonsoft_json_jsonconvert.htm - Einige kritische Hinweise zum Speichern von Json-Daten im SQL-Server
https://bornsql.ca/blog/think-twice-about-storing-json-in-your-sql-server-database/ - Repository des Beispiel-Quellcodes auf GitHub
https://github.com/RolandKoenig/HappyCoding/tree/main/2022/HappyCoding.JsonInSqlServer