JSON-Daten auslesen

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

[

Listing 1: Ausgabe des DOM eines JSON-Dokuments

• bolValues: Gibt an, ob hinter den jeweiligen Zeilen für die Pfade zu den Elementen noch die Werte angezeigt werden sollen.
• bolDebug: Gibt an, ob die Daten bei der Ausführung in den Direktbereich geschrieben werden sollen (dieser Parameter diente eher als Unterstützung beim Testen der Funktionen beim Programmieren).

Die Funktion GetJSONDOM deklariert zunächst eine Dictionary-Variable namens objJSON zum Aufnehmen des von der nachfolgend aufgerufenen Funktion ParseJSON zurückgelieferten Dictionary-Objekts. Außerdem deklariert sie eine weitere Dictionary-Variable namens dic und eine Collection-Variable namens col. Diese nehmen eventuell direkt in objJSON enthaltene Objekte des entsprechenden Typs auf. strKey nimmt im Falle eines Collection-Objekts die Schlüssel der enthaltenen Objekte auf.

Die Prozedur durchläuft nach Einlesen des übergeordneten Dictionary-Objekts alle darin enthaltenen Unterelemente. Dabei nutzen wir eine For…Next-Schleife, welche die Werte 0 bis zur Anzahl der Unterelemente minus 1 durchläuft. Die Anzahl ermitteln wir mit der Count-Eigenschaft.

Innerhalb dieser Schleife untersucht eine Select Case-Bedingung den Typ des aktuell durchlaufenen Elements, das wir mit objJSON.Items(i) referenzieren. Die TypeName-Funktion ermittelt den Typ des Elements, wobei wir auf die Werte Dictionary, Collection und andere stoßen können. Andere sind Name-Wert-Paare.

Finden wir ein Dictionary-Objekt als Unterobjekt vor, weisen wir das Objekt der Variablen dic zu und rufen die rekursiv definierte Funktion JSONDictionary auf. Dieser übergeben wir den Verweis auf das Dictionary-Objekt sowie den Namen des übergeordneten Elements, in diesem Fall objJSON, die Variable strJSONDOM, in der wir das Objektmodell zusammenstellen sowie die beiden optionalen Boolean-Parameter bolValues und bolDebug.

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo