Für XML gibt es eine eigene Bibliothek zum Lesen und Schreiben der Inhalte eines XML-Dokuments. Bei JSON sieht das anders aus: Microsoft hat bisher keine Bibliothek bereitgestellt, mit der man komfortabel auf ein JSON-Dokument zugreifen kann. Wir wollen diesen Missstand zumindest ein wenig lindern. Es gibt bereits ein Modul, welches den Inhalt einer JSON-Datei in eine Art Objektmodell einliest, das von VBA aus wesentlich einfacher zu lesen ist. Dieses greifen wir in diesem Beitrag auf und zeigen, wie wir die Struktur der enthaltenen Daten erfassen und damit besser und systematisch auswerten können.
Fertiges JSON-Modul
Das in der Einleitung angesprochene JSON-Modul finden Sie unter dem folgenden Link zum Download vor:
https://github.com/VBA-tools/VBA-JSON
Es ist aber auch in der Beispieldatenbank zu diesem Beitrag enthalten. Dieses Modul stellt eine für uns interessante Funktion namens ParseJSON zur Verfügung. Dieser übergibt man den Inhalt des JSON-Dokuments und erhält als Ergebnis ein Objekt des Typs Dictionary zurück. Damit wir dieses unter Verwendung von IntelliSense nutzen können, fügen wir dem VBA-Projekt einen Verweis auf die Bibliothek Microsoft Scripting Runtime hinzu (siehe Bild 1).
Bild 1: Verweis auf die Bibliothek Microsoft Scripting Runtime
Beispieldokument
Als Beispieldokument verwenden wir eines, das wir bei SelfHTML unter folgender Adresse gefunden haben:
https://wiki.selfhtml.org/wiki/JSON
Dieses Dokument sieht wie in Bild 2 aus. Dieses Beispiel ist übrigens bereits so formatiert, dass es relativ gut lesbar ist und man seine Struktur erkennen kann. Die JSON-Daten, welche die meisten Webservice-APIs zurückliefern, kommen meist ohne Einrückungen und Zeilenumbrüche. Unser Beispiel würde dann etwa wie folgt aussehen:
Bild 2: Beispiel für ein JSON-Dokument
{"name":"Georg","alter":47,"verheiratet":false,"beruf":null,"kinder":[{"name":"Lukas","alter":19,"schulabschluss":"Gymnasium"},{"name":"Lisa","alter":14,"schulabschluss":null}]}
Bereits diese kleine Datenmenge im JSON-Format ist für das menschliche Auge nicht gut lesbar.
JSON in ein Objektmodell überführen
Nun haben wir weiter oben bereits die Funktion ParseJSON erwähnt, welche den Inhalt einer JSON-Datei in ein übergeordnetes Dictionary-Objekt überführt. Dieses enthält einfache Eigenschaften, also Name-Wert-Paare, weitere Dictionary-Objekte sowie Collection-Objekte.
Das allein reicht eigentlich aus, um auf die Inhalte zuzugreifen. Das setzt allerdings voraus, dass man die Struktur kennt und wie man den Zugriff auf einzelne Elemente in der Struktur – oder auch auf mehrere Elemente unterhalb einer Dictionary– oder Collection-Auflistung – realisiert. Auf die einzelnen Werte des JSON-Beispiels können wir nach dem Eintragen in objJSON nämlich über die folgenden Ausdrücke zugreifen:
objJSON.Item("name") objJSON.Item("alter") objJSON.Item("verheiratet") objJSON.Item("beruf") objJson.Item("kinder").Item(1).Item("name") objJson.Item("kinder").Item(1).Item("alter") objJson.Item("kinder").Item(1).Item("schulabschluss") objJson.Item("kinder").Item(2).Item("name") objJson.Item("kinder").Item(2).Item("alter") objJson.Item("kinder").Item(2).Item("schulabschluss")
Wie wir systematisch auf die Elemente eines JSON-Dokuments zugreifen können, schauen wir uns am Ende des Beitrags an.
Zur Erläuterung: Die Elemente in geschweiften Klammern werden von ParseJSON in Dictionary-Objekte umgewandelt. Diejenigen in eckigen Klammern werden zu Collection-Objekten. Und die Name-Wert-Paare wie “name”: “Georg” werden einfach zu Elementen im Dictionary-Objekt, denen wir Name und Wert entnehmen können.
Hier erhalten wir also ein Dictionary-Objekt, das einige Unterelemente mit String-Werten enthält sowie eines, das als Element ein Collection-Element aufnimmt (Kinder). Dieses enthält wiederum zwei Dictionary-Objekte, eines für jedes Kind.
{ "name": "Georg", "alter": 47, "verheiratet": false, "beruf": null, "kinder": [ { "name": "Lukas", "alter": 19, "schulabschluss": "Gymnasium" }, { "name": "Lisa", "alter": 14, "schulabschluss": null } ] }
Wenn man die Grundregeln einmal verinnerlicht hat und das JSON-Dokument ordentlich formatiert vorliegen hat, kann man die für den Zugriff notwendigen Ausdrücke gegebenenfalls aus dem Kopf formulieren. Falls nicht, haben wir drei Routinen definiert, welche genau die Ausdrücke ausgeben, die für den Zugriff auf die im JSON-Dokument enthaltenen Elemente nötig sind.
Wir gehen davon aus, dass wir das JSON-Dokument auf irgendeine Weise als Zeichenkette erhalten. In der folgenden Beispielprozedur lesen wir dazu den Inhalt der Datei Beispiel.json aus dem gleichen Verzeichnis wie die Datenbank in die Variable strJSON ein.
Danach rufen wir die Prozedur GetJSONDOM auf, um die für den Zugriff auf das Document Object Model des JSON-Dokuments nötigen Ausdrücke zu ermitteln:
Public Sub JSONBeispiel() Dim strJSON As String Open CurrentProject.Path & "\Beispiel.json" For Input As #1 strJSON = Input$(LOF(1), #1) Close #1 GetJSONDOM strJSON, True End Sub
JSON-Dictionary auswerten
Die Funktion GetJSONDOM erwartet ein JSON-Dokument in Form einer String-Variablen als Parameter (siehe Listing 1). Außerdem können wir zwei weitere Parameter übergeben:
Public Function GetJSONDOM(strJSON As String, Optional bolValues As Boolean, Optional bolDebug As Boolean) As String Dim objJSON As Dictionary Dim dic As Dictionary Dim col As Collection Dim strKey As String Dim i As Integer Dim strJSONDOM As String Set objJSON = mdlJSON.ParseJson(strJSON) For i = 0 To objJSON.Count - 1 Select Case TypeName(objJSON.Items(i)) Case "Dictionary" Set dic = objJSON.Items(i) JSONDictionary dic, "objJSON", strJSONDOM, bolValues, bolDebug Case "Collection" Set col = objJSON.Items(i) strKey = ".Item(""" & objJSON.Keys(i) & """)" JSONCollection col, "objJson" & strKey, strJSONDOM, bolValues, bolDebug Case Else If Not bolValues Then If bolDebug Then Debug.Print "objJSON.Item(""" & objJSON.Keys(i) & """)" strJSONDOM = strJSONDOM & "objJSON.Item(""" & objJSON.Keys(i) & """)" & vbCrLf Else If bolDebug Then Debug.Print "objJSON.Item(""" & objJSON.Keys(i) & """):" & objJSON.Items(i) strJSONDOM = strJSONDOM & "objJSON.Item(""" & objJSON.Keys(i) & """):" & objJSON.Items(i) & vbCrLf End If End Select Next i GetJSONDOM = strJSONDOM End Function