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
	}
] 
Datenpaket 2: Id, Name und Reputation von drei Nutzern sowie jeweils einige ihrer Beiträge mit Angabe von Id, Erstellungsdatum und Anzahl der Antworten
[{ 
      "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

3
ORDIX bei der Ausbildungsmesse hobit in Darmstadt
Sicherheitsanalyse von Anwendungsservern

Related Posts

Unsere Autoren

Axel Röber
4 post(s)
Andre Dirr
4 post(s)
Dr. Stefan Koch
5 post(s)
Carsten Griese
3 post(s)

Technologie Blogs

Tutorials

4 members

Webentwicklung

3 members

Java

3 members

Archiv | Blog-Beiträge

Login