Convert JSON from hierarchical structure to flat structure?

991 views
Skip to first unread message

Richard Case

unread,
Nov 15, 2013, 4:10:14 AM11/15/13
to excel-r...@googlegroups.com
Hi all,

Thanks for putting together a great set of tools. I just started using it (and VB to be honest) yesterday and think it will be very powerful!

We have a JSON format like:
{"data":[{ , ... ,"stepResults":[{ , ... , }, ... ,{ , ... , }] } , ... , { , ... ,"stepResults":[{ , ... , }, ... ,{ , ... , }] } ] , ... , }

At the moment I have been able to use the standard cRest library to populate an excel sheet with one row for every "data" item using the single query.
However, what we need to do is have one row for every "stepResults" item, and we can either repeat the values relating to the "data" items in each row, or set them to null except for the first row. We're basically trying to do a LEFT OUTER JOIN in SQL speak.
The number of stepResults for each "data" item varies. Each data item has the same keys, and each stepResult has the same keys.

Please can someone tell me what the best way of doing this is, and maybe point me to one of the examples if it has been done before?

Many thanks in advance,
Rich

Bruce Mcpherson

unread,
Nov 15, 2013, 5:54:46 AM11/15/13
to excel-r...@googlegroups.com
Just change the restquery below to the real one, and make your headings match the properties on each element of stepresults, plus the ones you want to duplicate.

so the example below gives.. 
a sa sb
first a1 b1
first a2 b2
second c1 d1
second c2 d2




Private Sub testSr()
    Dim sFix As String, cr As cRest, outer As String, job As cJobject, joa As cJobject, _
        joe As cJobject, joc As cJobject, r As Range
    ' simulate some results
    outer = "stepResults"
    sFix = "{'data':[ " & _
    "{'a':'first','stepResults':[{'sa':'a1' ,'sb' :'b1'  }, { 'sa':'a2' ,'sb' :'b2' }]} " & _
    ",{'a':'second','stepResults':[{'sa':'c1' ,'sb' :'d1'  }, { 'sa':'c2' ,'sb' :'d2' }]} " & _
    "]  }"
    ' do a fake query and dont populate- replace this with the real query
    With restQuery("sr", , , , "dummyurl", "data", , False, , , True, sFix)
        
        ' so now we have the data, first step, clear any  existing data
        With .dset
            If Not .where Is Nothing Then .where.ClearContents
            Set r = firstCell(.headingRow.where)
        End With
    
        'next just go through each row
        For Each job In .datajObject.children
            Set joe = job.childExists(outer)
            ' must always have one I guess
            Debug.Assert Not joe Is Nothing
            ' now make a row for each item in the outer join
            
            For Each joc In joe.children
                ' assuming that every child of the inner array has been set up as a column heading.
                ' and that outer is an array
                Debug.Assert joc.isArrayMember
                Set r = r.Offset(1)
                ' duplicate the other children
                For Each joa In job.children
                    If joa.key <> outer Then
                        If Not .dset.headingRow.exists(joa.key) Is Nothing Then
                            r.Offset(, .dset.column(joa.key).column - 1).value = joa.value
                        End If
                    End If
                Next joa
                For Each joa In joc.children
                    If Not .dset.headingRow.exists(joa.key) Is Nothing Then
                        r.Offset(, .dset.column(joa.key).column - 1).value = joa.value
                    End If
                Next joa
            Next joc

        
        Next job
        
        ' and release any memory
        .tearDown
    End With
   
   
End Sub


--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Rich

unread,
Nov 15, 2013, 7:53:34 AM11/15/13
to excel-r...@googlegroups.com
Thanks for the fast response Bruce,

It appears to be failing because the headingRow.headings list is being cleared apart from the first one in the cr.execute() of restQuery().
Immediately after the init() the headings are okay.

Any ideas?

Thanks for the help!
Rich

Bruce Mcpherson

unread,
Nov 15, 2013, 9:24:10 AM11/15/13
to excel-r...@googlegroups.com
no - i dont understand . can you send me the code you are using along a sample data


--

Rich

unread,
Nov 19, 2013, 8:46:50 AM11/19/13
to excel-r...@googlegroups.com
Hi Bruce,

I tried it using your code by reading in my json from file and it works fine, so there must be some flag I'm setting or not setting in the real query that clears the headingRow list in the execute(). I will hopefully find time to debug further soon.

Bruce Mcpherson

unread,
Nov 19, 2013, 8:54:07 AM11/19/13
to excel-r...@googlegroups.com
ok - if you send me the exact code you are using for the query i can probably spot what the issue is

bruce

Rich

unread,
Nov 19, 2013, 1:56:17 PM11/19/13
to excel-r...@googlegroups.com
Here's some code snippets:

Your original that works using a hard-coded string:
Set cr = restQuery("Data", , , , "dummyurl", "data", , False, , , True, strTest)

The mostly successful query:
Set cr = restQuery("Data", libEntry, CStr(RunId), , , , , , , , , , Username, Password)
After this cr.dSet.headingRow.headings only has 1 item which is the top left column heading of the sheet.
There are 14 column headings and the first 5 of these are populated by the query already.
I have tried setting:
- bPopulate to False
- queryCanBeBlank to True

My libEntry:
        With .add("StepsFromRunId")
            .add "restType", erSingleQuery
            .add "url", "https://removed.us/webportal/api/report/campaignrunteststepresults?page=1&perpage=100&accountId=1&runid="
            .add "results", "data"
            .add "treeSearch", True
            .add "ignore", vbNullString
        End With

Thanks,
Rich

Bruce Mcpherson

unread,
Nov 19, 2013, 2:28:29 PM11/19/13
to excel-r...@googlegroups.com
bPopulate should be false, since you are going to populate the sheet directly.. querycanbeblank is irrelevant if query is not blank

here's some things to check

do you have any blank columns?
what does sad (cr.dSet.headingRow.where) look like after running the restquery

are you using http basic authentication there? is that working?

what does cr.jobject.serialize look like after the restquery
what about cr.datajobject.serialize

bruce



Reply all
Reply to author
Forward
0 new messages