07-24-2023, 10:02 AM
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.
[![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.