VBA-tools/VBA-JSON

Repeat last value on an Empty Record

DougChas opened this issue · 2 comments

DougChas commented

Multiple records are returned with each API call. Occasionally a sub-record will be returned empty. If in Record #1 the city name is "New York" and in Record #2 the city name is an empty record < empty record >, "New York" will be returned again as the city name for Record #2.

Json("myData")(Index)("placeOfPerformance")("city")("name"))

sHTTP = "https://api.website.name?limit=1000&api_key=" & MYKEY & "&postedFrom=" & FirstDATE "&postedTo=" & LastDATE
Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "GET", sHTTP
MyRequest.Send
Set Json = JsonConverter.ParseJson(MyRequest.ResponseText)

houghtonap commented

A few comments about your example code:

  1. VBA-JSON uses two different VBA objects to describe JSON Objects and JSON Arrays. It does not work like JavaScript where you can string references together. So your code Json("myData")(Index)("placeofPerformance")("city")("name") is most likely incorrect for what you think the JSON actually looks like in VBA-JSON.
  2. You probably should read my comment in issue #221 about the async parameter, it is most likely applicable to WinHTTP. Your .Open request is not specifying the async parameter (optional) and it probably defaults to TRUE (async) rather than FALSE (sync) for WinHTTP. The Microsoft documentation for WinHTTP is unclear on the default, MSXML2.XMLHTTP is most likely is using WinHTTP under the hood... so add FALSE as your third parameter to WinHTTP, otherwise catch the async call to avoid race conditions.
DougChas commented

I tried using FALSE ( MyRequest.Open "GET", sHTTP, False ) and unfortunately the Easy button did not work. :-( I used an online parser ( https://jsonparser.org/ ) on today's data. Here is what it looks like.

Json("myData")(Index)("placeOfPerformance")("city")("name"))

Index = 4

##################################
object {5}
...
myData [11]
...
4 {27}
...
placeOfPerformance {4}
city {2}
code : 00000
name : New York
state {2}
code : NY
name : New York
zip : 00000
country {2}
code : USA
name : UNITED STATES
...
##################################