Excel JSON VBA Parsing - Determining if an array is empty

I am trying to parse a JSON response. I cannot use the VBA-JSON library. I need to check to see if a nested array is empty or null. I keep getting this error:

[![enter image description here][1]][1]


[1]:

Example JSON:

{
"gardenAssets": [],
"gardenAssetsAlertCount": 0,
"gardenAssetsCount": 0,
"gardenAssetsErrorCount": 0,
"locationsSummaries": [
{
"locations": [
{
"auditOrder": "102",
"code": "POT 102",
"name": "POT 102",
"type": "ProcessingLocation",
"gardenAssets": [
{
"annotation": "Pallets",
"broker": {
"code": "TMTO",
"isOwner": null,
"name": null
},
"datetimeOfArrivalIngarden": 1622754283.937,
"id": "crusaf",
"isSealable": true,
"load": null,
"mastergardenCode": null,
"name": null,
"owner": {
"code": "SUN",
"isOwner": null,
"name": null
}
}
]
},
{
"auditOrder": "103",
"code": "POT 103",
"description": "POT 103",
"id": "110746",
"name": "POT 103",
"type": "ProcessingLocation",
"gardenAssets": []
},
{
"auditOrder": "104",
"code": "POT 104",
"name": "POT 104",
"gardenAssets": [
{
"annotation": "Soil",
"broker": {
"code": "OTHR",
"isOwner": null,
"name": null
},
"datetimeOfArrivalIngarden": 1622571699.767,
"id": "arserana",
"isSealable": true,
"load": null,
"mastergardenCode": null,
"name": null,
"owner": {
"code": "WTR",
"isOwner": null,
"name": null
}
}
]
},
{
"auditOrder": "111",
"code": "POT 111",
"name": "POT 111",
"type": "ProcessingLocation",
"gardenAssets": [
{
"annotation": null,
"broker": {
"code": "CLD",
"isOwner": null,
"name": null
},
"datetimeOfArrivalIngarden": 1622746446.932,
"id": "Bacrea",
"isSealable": true,
"load": null,
"mastergardenCode": null,
"name": null,
"owner": {
"code": "ICE",
"isOwner": null,
"name": null
},
"status": "EMPTY",
"type": "JUNK",
"unavailable": false,
"visitId": "1003768526"
}
]
}
],
"logingarden": true,
"mastergardenCodes": [],
"gardenCode": "FUN5"
}
],
"offsitegardens": [],
"gardenAssetsInTransit": []}

Code:
```
Option Explicit
Dim S as Object, k, Ks as Object
Set S = CreateObject("ScriptControl")
S.Language = "JScript"
S.addcode "function k(a){var k=[];for(var b in a){k.push('[\'' + b + '\']');}return k;}"

S.Eval ("var J = " & http.ResponseText)
S.Eval ("var L = J.locationsSummaries['0'].locations")
Set Ks = S.Eval("J.locationsSummaries['0'].locations")
For Each K In Ks
If Not IsNull(S.Eval(K.gardenAssets)) = True Then
Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1) = "Assets"
End If

Next K
```

I need to pull different information out of the JSON depending on if there are any gardenAssets. But I can't seem to check to see if the array is empty or not.

The example JSON isn't valid. The last member of an object or the last element of an array shouldn't have a comma after it. So where you have:

"broker": {
"code": "TMTO",
"isOwner": null,
"name": null,
}

There shouldn't be a comma after `"name": null` - there are multiple other errors like this in the example JSON.

You can use an online JSON validator (like [this one](

[To see links please register here]

)) to detect these errors. You would ideally want to fix the system that is generating this invalid JSON rather than trying to correct the issues yourself during processing

You can use the length property in JScript.
~~~~vba
Dim S As Object
Dim n As Integer, i As Integer, r As Long
r = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set S = CreateObject("ScriptControl")
With S
.Language = "JScript"
.eval "var J = " & http.ResponseText
.eval "var A = J.locationsSummaries['0'].locations"
For n = 1 To S.eval("A.length")
.eval "var L = A[" & n - 1 & "]"
For i = 1 To .eval("L.gardenAssets.length")
Sheet1.Cells(r, 1) = .eval("L.code")
Sheet1.Cells(r, 2) = .eval("L.gardenAssets[" & i - 1 & "].id")
r = r + 1
Next
Next
End With