Parse / flatten nested JSON string in VBA

Hi all,

I've been stumped on this for a while now and don't really know any good solution.
I'm parsing JSON in VBA via Jscript (the simple Set JSON = Script.Eval("(" + http.responsetext + ")") method) - which puts the http json response into an object). This works fine - IF you're working with a relatively flat JSON reply.

When I encounter a very nested / leveled JSON reply, I don't really know how to go around it - see below for a screenshot of what my 'parsed' JSON would look like in the locals window.
json.jpg

Now I know some of you might suggest me to use VBA-JSON, but that would still require me to know the structure and key names - whereas I just want it to be parsed without knowing that information.
I wrote/tried the code below, and while it almost does what I want, it messes up the columns where it puts stuff.

Sub GetValue(jO As Object, r As Long, c As Long)
Dim Key
For Each Key In S.Run("keys", jO)
If InStr(Key, "doc_count") > 0 Or InStr(Key, "length") > 0 Then GoTo nxk
If re = 1 Then c = c + 1: re = 0
If InStr(CallByName(jO, Key, VbGet), "[object Object]") > 0 Then
Call GetValue(CallByName(jO, Key, VbGet), r, c)
If re = 1 Then c = c - 1: re = 0
Else
If Not Sheet1.Cells(1, 1).Offset(r, c) = vbNullString Then r = r + 1
Sheet1.Cells(1, 1).Offset(r, c) = CallByName(jO, Key, VbGet)
If Not Key = "value" And Not InStr(CallByName(jO, Key, VbGet), "level") > 0 Then re = 1 Else c = c + 1
End If
re = 1

nxk:
Next Key
End Sub

This uses a global script control and 're' integer to determine whether to add a column or not.
While this -almost- works, I believe this is not the smartest way to do it, so would love any pointers.
Thanks,

Jasper

I see I am not alone in not knowing a solution to this
Bumping it up to hopefully spot Kyle123 eating christmas treats in a corner, while parsing some JSON through VBA


Would it in theory be possible to add javascript that translates json into a csv into VBA?
For example - this one: https://github.com/zemirco/json2csv

Hi Jasper, what would the output look like? This is hierarchical so I'm not really sure how you'd want it displayed in a grid.

Any change you could post the actual json?

Is this an elastic search result?

Hi Kyle,

thanks for your reply! It is company confidential, but I'll try to obfuscate the sensitive data and send to you over mail. Bear with me.
Thanks and merry christmas!

Jasper