Type Mismatch for JSON format in a list/array

[ParfaitG]

When attempting to parse a JSON type that is encapsulated in a list with [...] as shown below rather than key value pair, {...}, JSON.parse raises a runtime error for type mismatch on a ReDim line of an array.

Format

[
  {
    "key1": "value1",
    "key2": "value2",
    "key3": "value3"
  },
  {
    "key1": "value1",
    "key2": "value2",
    "key3": "value3"
  },
  {
    "key1": "value1",
    "key2": "value2",
    "key3": "value3"
  }
]

Reprex

Sub ParseCLDataJSON()
    Dim url As String, strJSON As String, strState As String
    Dim parsedJSON As Object, element As Variant

    url = "https://raw.githubusercontent.com/ParfaitG/DATA_MIGRATION/master/JSON/JSONtoCSV/CLData.json"

    ' Retrieve JSON response
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", url, True
        .Send
        Do Until .ReadyState = 4: DoEvents: Loop
         strJSON = .ResponseText
    End With

    ' Parse JSON response
    JSON.Parse strJSON, parsedJSON, strState

    ' ITERATE THROUGH DATA ROWS, APPENDING TO TABLE
    For Each element In parsedJSON
        Debug.Print element("user")
        Debug.Print element("category")
        Debug.Print element("city")
        Debug.Print element("post")
        Debug.Print element("time")
        Debug.Print element("link")
    Next element

    Set element = Nothing: Set parsedJSON = Nothing
End Sub

Error

Run-time error '13': Type mismatch

[danoxp]

Try passing Variant type to receive result of JSON.parse

Dim parsedJSON As Variant, element As Variant

parsedJSON returns Variant Array of Dictionary in your case.

IsArray(parsedJSON) 'True
IsObject(parsedJSON) 'False

hope this helps.

[ParfaitG]

That was it. Thanks @danoxp!