Loop through JSON files with specific notation

Question

I have three JSON files which I am looping through them to grab some data from. I have a problem with specific part in JSON file named "subject": In the first file there is no notation at all for this notation

In the second file there is a notation for subject like that

In the third file the subject notation like that

And in the code I am using I have used loops so as to get all the possible results

        For j = 1 To 11
            a(r, j + 47) = Json("@graph")(1)("subject")(j)("@value")
        Next j

It works for the third case and the first case .. but not for the case where there is only one notation

This is URL for the first case http://jsoneditoronline.org/?id=8c218c6a21184e9e864f5b4b6090c509

This is URL for the second case http://jsoneditoronline.org/?id=1f68983defef4ad9b4dccf335d15b1b2

This is URL for the third case http://jsoneditoronline.org/?id=e6e12e0aa1be4484b877924ca5fe2e9c

You will have to detect the type of data structure associated with the `"subject"` item. [This answer](https://stackoverflow.com/a/46245469/4717755) is a guide to show that an object with curly braces is a `Dictionary` and square brackets is a `Collection`. You can also use the `TypeName` function to figure out which type of object you have and then process the data accordingly. — PeterT, Jan 12 '20 at 23:23
Thanks a lot. Please if possible give me simple example of the three cases. If a notation not found at all in json file and if it is like a dictionary of one item and if it is a collection of dictionaries — YasserKhalil, Jan 12 '20 at 23:32

score 1 · Accepted Answer · answered Jan 13 '20 at 14:14

Despite the length of the code example below, it's relatively straightforward to check what kind of object you're accessing when looking for the "subject" (or any other JSON block). You just need to patiently check the possibilities.

I highly reccommend creating intermediate variables/objects to deal with and access the structured data in JSON. A long, stacked reference can be confusing to read and easy to lose track of where you are in the structure. For example

Json("@graph")(1)("subject")(j)("@value")

has several levels of hierarchical references to read, whereas if you do this

Dim graphData As Collection
Set graphData = json("@graph")

Dim graphSubject As Object
Set graphSubject = graphData.Item(1)("subject")

graphSubject(j)("@value")

I personally think it's easier to read and keep straight the different types of objects in the hierarchy than the continual stacked references.

I created three temporary JSON files, per your links above and parsed them accordingly, as shown in the example below.

Option Explicit

Sub ParseMyJSON()
    Dim jsonFilenames As Variant
    jsonFilenames = Array("json-subject1", "json-subject2", "json-subject3")

    Dim jsonFilename As Variant
    For Each jsonFilename In jsonFilenames
        Dim thisFilename As String
        thisFilename = "C:\Temp\" & jsonFilename & ".json"

        Dim json As Object
        Set json = GetJSON(thisFilename)

        Dim graphData As Collection
        Set graphData = json("@graph")

        '--- each of the two items in the graphData collection is
        '    a Dictionary, so check if the subject exists
        Debug.Print "processing " & thisFilename & ")"
        If graphData.Item(1).Exists("subject") Then
            Dim graphSubject As Object
            Set graphSubject = graphData.Item(1)("subject")
            If TypeName(graphSubject) = "Dictionary" Then
                Debug.Print vbTab & "language: " & graphSubject("@language")
                Debug.Print vbTab & "   value: " & graphSubject("@value")
            Else
                '--- this is a Collection, so there are multiple subjects
                Dim i As Long
                For i = 1 To graphSubject.Count
                    Debug.Print vbTab & "language(" & i & "): " & graphSubject(i)("@language")
                    Debug.Print vbTab & "   value(" & i & "): " & graphSubject(i)("@value")
                Next i
            End If
        Else
            Debug.Print vbTab & "subject does not exist in this graph!"
        End If
        Debug.Print ""

    Next jsonFilename
End Sub

Private Function GetJSON(ByVal filepath As String) As Object
    Dim jsonText As String
    Dim theFile As Long
    theFile = FreeFile
    Open filepath For Input As theFile
    jsonText = Input(LOF(theFile), theFile)
    Close theFile

    Set GetJSON = JsonConverter.ParseJson(jsonText)
End Function

Function HasKey(ByRef coll As Collection, ByVal strKey As String) As Boolean
    '--- from: https://stackoverflow.com/a/38040635/4717755
    Dim var As Variant
    On Error Resume Next
    var = coll(strKey)
    HasKey = (Err.Number = 0)
    Err.Clear
End Function

This example code produces the results output

processing C:\Temp\json-subject1.json)
    subject does not exist in this graph!

processing C:\Temp\json-subject2.json)
    language: por
       value: Pintura portuguesa, SÃ©c. 20

processing C:\Temp\json-subject3.json)
    language(1): por
       value(1): IndÃºstria de lanifÃcios, Portugal, SÃ©c. 19
    language(2): por
       value(2): IndÃºstria, Portugal, SÃ©c. 19
    language(3): por
       value(3): ÃndÃºstria de lanificios, Alentejo (Portugal), SÃ©c. 19

Thank you very much. That's awesome tutorial. I see HasKey UDF but you didn't use it .. Is it useful in this issue? — YasserKhalil, Jan 13 '20 at 15:28
Initially, I was using `HasKey` to check if a section (keyword) existed in a `Collection`. But a `Collection` in this JSON hierarchy is used as an array, so you'll just access each item in the collection with its index. So you don't really need it (and I should have deleted it from my code). — PeterT, Jan 13 '20 at 15:54

Loop through JSON files with specific notation

1 Answers1

Linked