Convert JSON from hierarchical structure to flat structure?

[Richard Case]

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]

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]

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]

no - i dont understand . can you send me the code you are using along a sample data

--

[Rich]

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]

ok - if you send me the exact code you are using for the query i can probably spot what the issue is

bruce

[Rich]

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]

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