Import von JSON-formatierten Informationen in den Microsoft SQL Server
Zumindest in meinem Projektumfeld kommt es immer wieder vor, dass mal eben schnell ein paar Daten aus einer Datenbank ermittelt und per Mail an andere Personen zu übertragen sind.
Wenn es sich um komplexere Datenstrukturen handelt, dann stellt sich immer die Frage: In welchem Format sollen die Daten bereitgestellt werden. Statt der klassischen Wege der CSV-Datei oder Excel-Tabelle möchte ich in diesem Artikel JSON als Alternative vorstellen.
Dies ist die Fortsetzung zum ersten Teil: JSON als elegante Formatierung von Abfrage-Ergebnissen zum schnellen Datenaustausch.
Vorab: Meine Beispieldaten
Hier zunächst ein korrekt als JSON-formatierter Auszug der im ersten Teil erzeugten Daten.
Datenpaket 1: Id, Name und Reputation von fünf Nutzern
[{
"Id": 22656,
"DisplayName": "Jon Skeet",
"Reputation": 990402
}, {
"Id": 29407,
"DisplayName": "Darin Dimitrov",
"Reputation": 768383
}, {
"Id": 157882,
"DisplayName": "BalusC",
"Reputation": 766766
}, {
"Id": 6309,
"DisplayName": "VonC",
"Reputation": 730237
}, {
"Id": 17034,
"DisplayName": "Hans Passant",
"Reputation": 728015
}
]
[{
"User": {
"Id": 22656,
"DisplayName": "Jon Skeet",
"Reputation": 990402 },
"Posts": [{
"Id": 194484,
"CreationDate": "2008-10-11T19:30:45.407",
"AnswerCount": 37
}, {
"Id": 215548,
"CreationDate": "2008-10-18T20:47:40.003",
"AnswerCount": 42
}, {
"Id": 3438806,
"CreationDate": "2010-08-09T09:40:42.517",
"AnswerCount": 10
}, {
"Id": 3657778,
"CreationDate": "2010-09-07T10:27:17.253",
"AnswerCount": 9
}
]
}, {
"User": {
"Id": 29407,
"DisplayName": "Darin Dimitrov",
"Reputation": 768383 },
"Posts": [{
"Id": 269988,
"CreationDate": "2008-11-06T19:40:47.670",
"AnswerCount": 6
}, {
"Id": 311696,
"CreationDate": "2008-11-22T19:51:53.737",
"AnswerCount": 5
}, {
"Id": 3220242,
"CreationDate": "2010-07-10T17:47:54.397",
"AnswerCount": 6
}
]
}, {
"User": {
"Id": 69083,
"DisplayName": "Guffa",
"Reputation": 504630 },
"Posts": [{
"Id": 2643812,
"CreationDate": "2010-04-15T08:41:19.703",
"AnswerCount": 2
}, {
"Id": 2740709,
"CreationDate": "2010-04-29T20:53:45.680",
"AnswerCount": 1
}
]
}
]
Aufbau der Zieltabellen
Um beide Datenpakete importieren zu können, werden diese Tabellen benötigt:
CREATE TABLE UserReputation ( Id INT PRIMARY KEY , DisplayName VARCHAR(100) , Reputation INT ); CREATE TABLE UserPosts ( Id INT PRIMARY KEY , CreationDate DATETIME , AnswerCount INT , UserId INT FOREIGN KEY REFERENCES UserReputation (Id) );
Import einer flachen JSON-Struktur
Das erste Datenpaket enthält lediglich Objekte eines Typs und kann daher in eine einzelne Tabelle importiert werden.
Vor dem INSERT schauen wir uns die Daten aber erst einmal an. Die OPENJSON-Funktion zerlegt das Datenpaket in die einzelnen Objekte und zeigt jedes Objekt als eine Zeile an.
DECLARE @JsonString AS VARCHAR(MAX) = '[{"Id":22656,"DisplayName":"Jon Skeet","Reputation":990402},{"Id":29407,"DisplayName":"Darin Dimitrov","Reputation":768383},{"Id":157882,"DisplayName":"BalusC","Reputation":766766},{"Id":6309,"DisplayName":"VonC","Reputation": 730237},{"Id":17034,"DisplayName":"Hans Passant","Reputation":728015}]'
SELECT * FROM OPENJSON(@JsonString)
Jedes Objekt ist weiterhin ein JSON-String, der in einer Spalte mit dem Namen „value" ausgegeben wird.
Durch die Angabe der optionalen WITH-Klausel kann das Ausgabeschema definiert werden, wir können uns so also die Attribute der Objekte in einzelnen Spalten ausgeben lassen.
DECLARE @JsonString AS VARCHAR(MAX) = '[{"Id":22656,"DisplayName":"Jon Skeet","Reputation":990402},{"Id":29407,"DisplayName":"Darin Dimitrov","Reputation":768383},{"Id":157882,"DisplayName":"BalusC","Reputation":766766},{"Id":6309,"DisplayName":"VonC","Reputation": 730237},{"Id":17034,"DisplayName":"Hans Passant","Reputation":728015}]'
SELECT *
FROM OPENJSON(@JsonString)
WITH ( Id INT
, DisplayName VARCHAR(100)
, Reputation INT
)
So ist es nur noch ein kleiner Schritt, die Daten in die Tabelle einzufügen.
DECLARE @JsonString AS VARCHAR(MAX) = '[{"Id":22656,"DisplayName":"Jon Skeet","Reputation":990402},{"Id":29407,"DisplayName":"Darin Dimitrov","Reputation":768383},{"Id":157882,"DisplayName":"BalusC","Reputation":766766},{"Id":6309,"DisplayName":"VonC","Reputation": 730237},{"Id":17034,"DisplayName":"Hans Passant","Reputation":728015}]'
INSERT INTO UserReputation
SELECT *
FROM OPENJSON(@JsonString)
WITH ( Id INT
, DisplayName VARCHAR(100)
, Reputation INT
)
Import einer geschachtelten JSON-Struktur
Einer der großen Vorteile des JSON-Formates ist es, dass auch komplexe Strukturen in einem Dokument verwendet werden können. In unserem Fall werden zu jedem Nutzer noch mehrere Beiträge gespeichert, es handelt sich also um eine klassische 1:n-Relation.
Auch hier zunächst die Ausgabe von OPENJSON.
DECLARE @JsonString AS NVARCHAR(MAX) = '[{"User":{"Id":22656,"DisplayName":"Jon Skeet","Reputation":990402},"Posts":[{"Id":194484,"CreationDate":"2008-10-11T19:30:45.407","AnswerCount":37},{"Id":215548,"CreationDate":"2008-10-18T20:47:40.003","AnswerCount":42},{"Id":3438806,"CreationDate":"2010-08-09T09:40:42.517","AnswerCount":10},{"Id":3657778,"CreationDate":"2010-09-07T10:27:17.253","AnswerCount":9}]},{"User":{"Id":29407,"DisplayName":"Darin Dimitrov","Reputation":768383},"Posts":[{"Id":269988,"CreationDate":"2008-11-06T19:40:47.670","AnswerCount":6},{"Id":311696,"CreationDate":"2008-11-22T19:51:53.737","AnswerCount":5},{"Id":3220242,"CreationDate":"2010-07-10T17:47:54.397","AnswerCount":6}]},{"User":{"Id":69083,"DisplayName":"Guffa","Reputation":504630},"Posts":[{"Id":2643812,"CreationDate":"2010-04-15T08:41:19.703","AnswerCount":2},{"Id":2740709,"CreationDate":"2010-04-29T20:53:45.680","AnswerCount":1}]}]'
SELECT * FROM OPENJSON(@JsonString)
Hier bekommen wir wieder eine Zeile pro JSON-Objekt. Diese JSON-Objekte bestehen aber wiederum aus zwei weiteren Objekten: „User" und „Posts", deren Inhalt wiederum JSON-Objekte sind.
Die DECLARE-Zeile lasse ich im Folgenden jeweils weg, damit es hier übersichtlicher ist. Das nächste SQL lautet also:
SELECT *
FROM OPENJSON(@JsonString) WITH ( [User] NVARCHAR(MAX) AS JSON
, Posts NVARCHAR(MAX) AS JSON
)
GO
Zu beachten ist hier, dass „User" ein reserviertes Wort ist und daher in eckige Klammern gesetzt werden muss. Zudem wird der Zusatz „AS JSON" benötigt, damit der SQL Server das Ergebnis richtig interpretiert.
Um nun an die einzelnen Informationen zu einem Benutzer zu gelangen, muss der Inhalt der Spalte „User" wieder mit der Funktion OPENJSON zerlegt werden. Hier nutze ich die Funktionalität „CROSS APPLY".
SELECT [User].*, Posts
FROM OPENJSON(@JsonString) WITH ( [User] NVARCHAR(MAX) AS JSON
, Posts NVARCHAR(MAX) AS JSON
) AS UserAndPosts
CROSS APPLY OPENJSON(UserAndPosts.[User]) WITH ( Id INT
, DisplayName VARCHAR(100)
, Reputation INT
) AS [User]
Auch zur Zerlegung der Beiträge kann diese Funktion genutzt werden, indem einfach ein weiteres CROSS APPLY angefügt wird.
SELECT [User].*, Posts.*
FROM OPENJSON(@JsonString) WITH ( [User] NVARCHAR(MAX) AS JSON
, Posts NVARCHAR(MAX) AS JSON
) AS UserAndPosts
CROSS APPLY OPENJSON(UserAndPosts.[User]) WITH ( Id INT
, DisplayName VARCHAR(100)
, Reputation INT
) AS [User]
CROSS APPLY OPENJSON(UserAndPosts.Posts) WITH ( Id INT
, CreationDate DATETIME
, AnswerCount INT
) AS Posts
GO
Damit steigt allerdings die Anzahl der ausgegebenen Datensätze an, da für jeden Beitrag eine Zeile erzeugt wird. Die Informationen zu den Benutzern erscheinen dabei redundant.
Um nun den Import in die Tabellen vornehmen zu können, müssen lediglich beide Abfragen nacheinander ausgeführt werden: Einmal nur die Benutzer und einmal die Beiträge mit zusätzlicher Angabe der Benutzer-Id.
INSERT INTO UserReputation
SELECT [User].*
FROM OPENJSON(@JsonString) WITH ( [User] NVARCHAR(MAX) AS JSON
, Posts NVARCHAR(MAX) AS JSON
) AS UserAndPosts
CROSS APPLY OPENJSON(UserAndPosts.[User]) WITH ( Id INT
, DisplayName VARCHAR(100)
, Reputation INT
) AS [User]
INSERT INTO UserPosts
SELECT Posts.*, [User].Id AS UserId
FROM OPENJSON(@JsonString) WITH ( [User] NVARCHAR(MAX) AS JSON
, Posts NVARCHAR(MAX) AS JSON
) AS UserAndPosts
CROSS APPLY OPENJSON(UserAndPosts.[User]) WITH ( Id INT
, DisplayName VARCHAR(100)
, Reputation INT
) AS [User]
CROSS APPLY OPENJSON(UserAndPosts.Posts) WITH ( Id INT
, CreationDate DATETIME
, AnswerCount INT
) AS Posts
Fazit
Mit wenig Aufwand kann das Ergebnis einer Abfrage in ein JSON-Dokument umgewandelt werden und mit ebenso wenig Aufwand auch wieder in eine relationale Datenbank importiert werden.
Auch wenn ich die Funktionalität hier am Beispiel des Microsoft SQL Servers gezeigt habe, so ist dies auch bei anderen relationalen Datenbanken möglich.
Links/Quellen
JSON-Daten in SQL Server: https://docs.microsoft.com/de-de/sql/relational-databases/json/json-data-sql-server
Dokumentation der Funktion OPENJSON: https://docs.microsoft.com/de-de/sql/t-sql/functions/openjson-transact-sql
Principal Consultant bei ORDIX
Bei Updates im Blog, informieren wir per E-Mail.
Kommentare