I got stuck on the child list

[cityba]

@omegastripes Hi, I got stuck and I would ask for help. A Json resp. I have a "..line" child, it's a variable number, I want to write this child's "lineNumber", "lineDescription" to the table. No matter how hard I try, I get here:

`...Dim i As Integer Dim Item As Variant 'head = Array("Main.Lines.line[0].lineDescription") Dim rows As Dictionary Set rows = New Dictionary For i = 0 To UBound(data) For Each Item In data(i)("Main")("Lines")("line") 'Debug.Print i

    Set data(i)("Main.Lines.line[i]") = rows
     Next
Next
' specify fields neccessary to display
head = Array("Number", "Main.Lines.line.lineDescription", "rows?[i]")
 jsonExt.pushItem data, rows, , False

Dim body()
jsonExt.toArray data, body, head, True
' output arrays to worksheet

With ThisWorkbook.Sheets("szamla")
    .Activate
    .Cells.Delete
    With .Cells(1, 1)
        .Resize(1, UBound(head) - LBound(head) + 1).Value = head
        .Offset(1, 0).Resize( _
                UBound(body, 1) - LBound(body, 1) + 1, _
                UBound(body, 2) - LBound(body, 2) + 1 _
            ).Value = body
    End With...

`

[omegastripes]

@cityba Could you please provide some JSON fragment that could be fed to the parser (with valid syntax), and screenshot of the resulting table populated manually which corresponds that JSON fragment.

[cityba]

@omegastripes I want to print lineDesc to several lines. The the number of rows varies.

[cityba]

that would be the goal. how can i list this, print lineDescription?

[omegastripes]

So... I need JSON sample to test in parser, not JSON screenshot)

[omegastripes]

Anyway it's better not to modify initial array data and convert it to array for output, but for simplicity build brand new array of dicts with necessary props which you copy from data within nested loops, and then convert it to array for output.

[cityba]

@omegastripes did you think of this? I don't really understand, maybe you put in an example code how do I print the lineDescription value? thanks in advance for the help. {"rows":[{"invoiceNumber":"bla/2158016","invoiceIssueDate":"2021-11-02","completenessIndicator":"false","invoiceMain":{"invoice":{"invoiceHead":{"supplierInfo":{"supplierTaxNumber":{"taxpayerId":"135","vatCode":"2","countyCode":"42"},"supplierName":"bla KFT.","supplierAddress":{"simpleAddress":{"countryCode":"HU","postalCode":"1089","city":"Budst","additionalAddressDetail":"De utca 1."}}},"customerInfo":{"customerVatStatus":"DOMESTIC","customerVatData":{"customerTaxNumber":{"taxpayerId":"21222","vatCode":"2","countyCode":"43"}},"customerName":"SZM KT.","customerAddress":{"simpleAddress":{"countryCode":"HU","postalCode":"1","city":"Bpest","additionalAddressDetail":"H UT 7."}}},"invoiceDetail":{"invoiceCategory":"NORMAL","invoiceDeliveryDate":"2021-11-02","currencyCode":"HUF","exchangeRate":"1","selfBillingIndicator":"false","paymentMethod":"CASH","paymentDate":"2021-11-02","cashAccountingIndicator":"false","invoiceAppearance":"PAPER"}},"invoiceLines":{"mergedItemIndicator":"false","line":[{"lineNumber":"1","productCodes":{"productCode":{"productCodeCategory":"OWN","productCodeOwnValue":"D1101"}},"lineExpressionIndicator":"false","lineDescription":"MÜDS 100 DOBOZ POÁLT ALJ","quantity":"5","unitOfMeasure":"PIECE","unitPrice":"130.64","lineAmountsNormal":{"lineNetAmountData":{"lineNetAmount":"653.2","lineNetAmountHUF":"653.2"},"lineVatRate":{"vatPercentage":"0.27"},"lineVatData":{"lineVatAmount":"176.36","lineVatAmountHUF":"176.36"},"lineGrossAmountData":{"lineGrossAmountNormal":"829.56","lineGrossAmountNormalHUF":"829.56"}},"additionalLineData":{"dataName":"T11_TETESZ","dataDescription":"belso tetelazonosito","dataValue":"4594462"}},{"lineNumber":"2","productCodes":{"productCode":{"productCodeCategory":"OWN","productCodeOwnValue":"D6071"}},"lineExpressionIndicator":"false","lineDescription":"M5 DOBOZ SOLHÓ SÁACSAAR NÉLKÜL 200db/csomag","quantity":"50","unitOfMeasure":"PIECE","unitPrice":"32.2","lineAmountsNormal":{"lineNetAmountData":{"lineNetAmount":"1610","lineNetAmountHUF":"1610"},"lineVatRate":{"vatPercentage":"0.27"},"lineVatData":{"lineVatAmount":"434.7","lineVatAmountHUF":"434.7"},"lineGrossAmountData":{"lineGrossAmountNormal":"2044.7","lineGrossAmountNormalHUF":"2044.7"}},"additionalLineData":{"dataName":"T11_TETELSSZ","dataDescription":"belso tetelazonosito","dataValue":"4594463"}},{"lineNumber":"3","productCodes":{"productCode":{"productCodeCategory":"OWN","productCodeOwnValue":"D8001"}},"lineExpressionIndicator":"false","lineDescription":"MÜ 80 SPECDOZ ALJ (SÁRGA)","quantity":"20","unitOfMeasure":"PIECE","unitPrice":"57.96","lineAmountsNormal":{"lineNetAmountData":{"lineNetAmount":"1159.2","lineNetAmountHUF":"1159.2"},"lineVatRate":{"vatPercentage":"0.27"},"lineVatData":{"lineVatAmount":"312.98","lineVatAmountHUF":"312.98"},"lineGrossAmountData":{"lineGrossAmountNormal":"1472.18","lineGrossAmountNormalHUF":"1472.18"}},"additionalLineData":{"dataName":"T11_TETELSSZ","dataDescription":"belso tetelazonosito","dataValue":"4594464"}},{"lineNumber":"4","productCodes":{"productCode":{"productCodeCategory":"OWN","productCodeOwnValue":"CSŐIII16"}},"lineExpressionIndicator":"false","lineDescription":"MÜ III 1DŐCSRKE 2,5m/SZÁL mÖTEG","quantity":"100","unitOfMeasure":"METER","unitPrice":"54.15","lineAmountsNormal":{"lineNetAmountData":{"lineNetAmount":"5415","lineNetAmountHUF":"5415"},"lineVatRate":{"vatPercentage":"0.27"},"lineVatData":{"lineVatAmount":"1462.05","lineVatAmountHUF":"1462.05"},"lineGrossAmountData":{"lineGrossAmountNormal":"6877.05","lineGrossAmountNormalHUF":"6877.05"}},"additionalLineData":{"dataName":"T11_TETELSSZ","dataDescription":"belso tetelazonosito","dataValue":"4594465"}},{"lineNumber":"5","productCodes":{"productCode":{"productCodeCategory":"OWN","productCodeOwnValue":"STUKATUR 20KG"}},"lineExpressionIndicator":"false","lineDescription":"GIPSZ 20KG/ZSÁK (54zsák/raklap)","quantity":"2","unitOfMeasure":"PIECE","unitPrice":"1490","lineAmountsNormal":{"lineNetAmountData":{"lineNetAmount":"2980","lineNetAmountHUF":"2980"},"lineVatRate":{"vatPercentage":"0.27"},"lineVatData":{"lineVatAmount":"804.6","lineVatAmountHUF":"804.6"},"lineGrossAmountData":{"lineGrossAmountNormal":"3784.6","lineGrossAmountNormalHUF":"3784.6"}},"additionalLineData":{"dataName":"T11_TETELSSZ","dataDescription":"belso tetelazonosito","dataValue":"4594467"}}]},"invoiceSummary":{"summaryNormal":{"summaryByVatRate":{"vatRate":{"vatPercentage":"0.27"},"vatRateNetData":{"vatRateNetAmount":"11817.4","vatRateNetAmountHUF":"11817.4"},"vatRateVatData":{"vatRateVatAmount":"3190.7","vatRateVatAmountHUF":"3190.7"},"vatRateGrossData":{"vatRateGrossAmount":"15008.1","vatRateGrossAmountHUF":"15008.1"}},"invoiceNetAmount":"11817.4","invoiceNetAmountHUF":"11817.4","invoiceVatAmount":"3190.7","invoiceVatAmountHUF":"3190.7"},"summaryGrossData":{"invoiceGrossAmount":"15008.1","invoiceGrossAmountHUF":"15008"}}}}}]}

[cityba]

@omegastripes these would be the paths, how can I put them in the table? ..line[i]...?

`rows[2].invoiceMain.invoice.invoiceLines.line[0].lineNumber rows[2].invoiceMain.invoice.invoiceLines.line[0].lineDescription .. rows[2].invoiceMain.invoice.invoiceLines.line[1].lineNumber rows[2].invoiceMain.invoice.invoiceLines.line[1].lineDescription

.. `

[cityba]

@omegastripes it works sometimes, but it often stops at fault 13. ` ..For Each Item In content("rows") Sheets("szamla").Cells(i, 1).Value = Item("invoiceNumber")

   j = 1
   For Each Itemm In Item("invoiceMain")("invoice")("invoiceLines")("line")      

   Sheets("szamla").Cells(i, 2).Value = Itemm("lineNumber")
   Sheets("szamla").Cells(i, 3).Value = Itemm("lineDescription")

   j = j + 1
   i = i + 1
     Next
    i = i + 1
     Next..`

[omegastripes]

Check each level to avoid errors. Here is the example:

Sub testSample()

    Dim sample
    sample = loadTextFromFile(ThisWorkbook.path & "\sample.json", "utf-8")
    Dim data
    Dim state As String
    JSON.Parse sample, data, state
    If state <> "Object" Then Exit Sub
    If Not IsArray(data("rows")) Then Exit Sub
    Dim resultRows
    resultRows = Array()
    Dim lineArrPath
    lineArrPath = ".invoiceMain.invoice.invoiceLines.line"
    Dim dataRow
    For Each dataRow In data("rows")
        Dim lineElems, lineElemsExists
        jsonExt.selectElement dataRow, lineArrPath, lineElems, lineElemsExists
        If Not lineElemsExists Then
        ElseIf Not IsArray(lineElems) Then
        Else
            Dim lineElem
            For Each lineElem In lineElems
                If Not IsObject(lineElem) Then
                ElseIf Not lineElem.exists("lineDescription") Then
                Else
                    Dim resultRow
                    Set resultRow = New Dictionary
                    resultRow("invoiceNumber") = dataRow("invoiceNumber")
                    resultRow("lineDescription") = lineElem("lineDescription")
                    jsonExt.pushItem resultRows, resultRow
                End If
            Next
        End If
    Next
    If UBound(resultRows) < 0 Then Exit Sub
    Output ThisWorkbook.Sheets(1), resultRows

End Sub

Please find attached sample json and workbook with working code in module TestSample. example.xlsm.zip sample.json.zip

[cityba]

@omegastripes Thank you so much! for expanding the list for why not return results? How is it possible to expand the query? for example: resultRow("unitPriceHUF") = lineElem("unitPriceHUF") resultRow("lineAmountsNormal.lineGrossAmountData.lineGrossAmountNormalHUF") = lineElem("lineAmountsNormal.lineGrossAmountData.lineGrossAmountNormalHUF")

[omegastripes]

In VBA dictionary it's possible to access elements by key, not by path stretching through levels. Use jsonExt.selectElement method call as in the above example instead of direct access by path lineElem("lineAmountsNormal.lineGrossAmountData.lineGrossAmountNormalHUF")

[omegastripes]

Like this

Sub testSample()

    Dim sample
    sample = loadTextFromFile(ThisWorkbook.path & "\sample.json", "utf-8")
    Dim data
    Dim state As String
    JSON.Parse sample, data, state
    If state <> "Object" Then Exit Sub
    If Not IsArray(data("rows")) Then Exit Sub
    Dim resultRows
    resultRows = Array()
    Dim lineArrPath
    lineArrPath = ".invoiceMain.invoice.invoiceLines.line"
    lineGrossPath = ".lineAmountsNormal.lineGrossAmountData.lineGrossAmountNormalHUF"
    Dim dataRow
    For Each dataRow In data("rows")
        Dim lineElems, lineElemsExists
        jsonExt.selectElement dataRow, lineArrPath, lineElems, lineElemsExists
        If Not lineElemsExists Then
        ElseIf Not IsArray(lineElems) Then
        Else
            Dim lineElem
            For Each lineElem In lineElems
                If Not IsObject(lineElem) Then
                ElseIf Not lineElem.exists("lineDescription") Then
                Else
                    Dim resultRow
                    Set resultRow = New Dictionary
                    resultRow("invoiceNumber") = dataRow("invoiceNumber")
                    resultRow("lineDescription") = lineElem("lineDescription")
                    If lineElem.exists("unitPriceHUF") Then
                        resultRow("unitPriceHUF") = lineElem("unitPriceHUF")
                    End If
                    Dim lineGross, lineGrossExists
                    jsonExt.selectElement lineElem, lineGrossPath, lineGross, lineGrossExists
                    If Not lineElemsExists Then
                    ElseIf Not jsonExt.isScalar(lineGross) Then
                    Else
                        resultRow("lineGrossAmountNormalHUF") = lineGross
                    End If
                    jsonExt.pushItem resultRows, resultRow
                End If
            Next
        End If
    Next
    If UBound(resultRows) < 0 Then Exit Sub
    Output ThisWorkbook.Sheets(1), resultRows

End Sub

[cityba]

@omegastripes thanks, i tried eg with the tax-id but it doesn't do anything, not work: `Dim data Dim state As String JSON.Parse resp, data, state If state <> "Object" Then Exit Sub If Not IsArray(data("rows")) Then Exit Sub Dim resultRows resultRows = Array() Dim lineArrPath Dim lineGrossPath Dim lineSupPath lineSupPath = ".invoiceMain.invoice.invoiceHead.supplierInfo.supplierTaxNumber" lineArrPath = ".invoiceMain.invoice.invoiceLines.line" lineGrossPath = ".lineAmountsNormal.lineGrossAmountData.lineGrossAmountNormalHUF"

Dim dataRow
For Each dataRow In data("rows")
    Dim lineElems, lineElemsExists
    jsonExt.selectElement dataRow, lineArrPath, lineElems, lineElemsExists
    If Not lineElemsExists Then
    ElseIf Not IsArray(lineElems) Then
    Else
        Dim lineElem
        For Each lineElem In lineElems
            If Not IsObject(lineElem) Then
            ElseIf Not lineElem.exists("lineDescription") Then
            Else
                Dim resultRow
                Set resultRow = New Dictionary
                resultRow("invoiceNumber") = dataRow("invoiceNumber")
                resultRow("lineDescription") = lineElem("lineDescription")

                If lineElem.exists("unitPriceHUF") Then
                    resultRow("unitPriceHUF") = lineElem("unitPriceHUF")
                End If

                Dim lineGross, lineGrossExists
                jsonExt.selectElement lineElem, lineGrossPath, lineGross, lineGrossExists

                If Not lineElemsExists Then
                ElseIf Not jsonExt.isScalar(lineGross) Then
                Else
                    resultRow("lineGrossAmountNormalHUF") = lineGross
                End If

                Dim lineSup, lineSupExists
                jsonExt.selectElement lineElem, lineSupPath, lineSup, lineSupExists
                If Not lineElemsExists Then
                ElseIf Not jsonExt.isScalar(lineSup) Then
                Else
                    resultRow("taxpayerId") = lineSup
                End If

                jsonExt.pushItem resultRows, resultRow
            End If
        Next
    End If
Next
If UBound(resultRows) < 0 Then Exit Sub`

if I need more data than each one, should I do so? eg tax-id, supplier address, city, vat, etc.?

[cityba]

@omegastripes hi, thanks for the help, maybe if I want to call all the data, the lines ..Head..and the Lines .. at the same time, how can I do that?

[omegastripes]

If you need to keep all data displayed on the sheet and output each line separately then it's necessary to unpivot line arrays in each row object. The parser doesn't provide unpivot methods at the moment, so that takes extra effort.

[omegastripes]

Sub testSample1()

    Dim sample
    sample = loadTextFromFile(ThisWorkbook.path & "\sample.json", "utf-8")
    Dim data
    Dim state As String
    JSON.Parse sample, data, state
    If state <> "Object" Then Exit Sub
    If Not IsArray(data("rows")) Then Exit Sub
    Dim resultRows
    resultRows = Array()
    Dim lineArrPath
    lineArrPath = ".invoiceMain.invoice.invoiceLines.line"
    Dim dataRow
    For Each dataRow In data("rows")
        ' retrieve line array from row object
        Dim lineElems, lineElemsExists
        jsonExt.selectElement dataRow, lineArrPath, lineElems, lineElemsExists
        If Not lineElemsExists Then
        ElseIf jsonExt.safeUBound(lineElems) = -1 Then
        Else
            ' remove line array from row object
            dataRow("invoiceMain")("invoice")("invoiceLines").Remove "line"
            ' flatten the rest of row object
            Dim dataRowFlat
            Set dataRowFlat = jsonExt.flatten(dataRow)
            ' unpivot line array
            Dim lineElem
            For Each lineElem In lineElems
                If Not IsObject(lineElem) Then
                Else
                    ' flatten line object
                    Dim lineElemFlat
                    Set lineElemFlat = jsonExt.flatten(lineElem)
                    ' join row and line data into result row
                    Dim resultRow
                    Set resultRow = New Dictionary
                    jsonExt.joinDicts resultRow, dataRowFlat
                    jsonExt.joinDicts resultRow, lineElemFlat
                    ' push to result array
                    jsonExt.pushItem resultRows, resultRow
                End If
            Next
        End If
    Next
    If UBound(resultRows) < 0 Then Exit Sub
    Output ThisWorkbook.Sheets(1), resultRows

End Sub

example.xlsm.zip

[cityba]

@omegastripes Super, thank you very much for your help. unfortunately i don't really understand how the code works so i got stuck. thank you.

[omegastripes]

I added comments in the code, so you may point where the code unclear from, and I could elaborate.

[cityba]

@omegastripes So don't print those that have only 1 line. Why is that? only ..lineNumber: 1 its not echo sheets.

[omegastripes]

I tried the sample above you provided modified to single line and it works OK. So please share your sample.json.zip file having such single line rows.

[cityba]

@omegastripes this not work : sample.zip

[omegastripes]

I inspected sample and find that single line isn't wrapped into array, thus need to check if .invoiceMain.invoice.invoiceLines.line is array.

Sub testSample1()

    Dim sample
    sample = loadTextFromFile(ThisWorkbook.path & "\sample1.json", "utf-8")
    Dim data
    Dim state As String
    JSON.Parse sample, data, state
    If state <> "Object" Then Exit Sub
    If Not IsArray(data("rows")) Then Exit Sub
    Dim resultRows
    resultRows = Array()
    Dim lineArrPath
    lineArrPath = ".invoiceMain.invoice.invoiceLines.line"
    Dim dataRow
    For Each dataRow In data("rows")
        ' retrieve line array from row object
        Dim lineElems, lineElemsExists
        jsonExt.selectElement dataRow, lineArrPath, lineElems, lineElemsExists
        If lineElemsExists Then
            ' wrap single element into array to keep consistence
            If IsObject(lineElems) Then
                lineElems = Array(lineElems)
            End If
            If jsonExt.safeUBound(lineElems) > -1 Then
                ' remove line array from row object
                dataRow("invoiceMain")("invoice")("invoiceLines").Remove "line"
                ' flatten the rest of row object
                Dim dataRowFlat
                Set dataRowFlat = jsonExt.flatten(dataRow)
                ' unpivot line array
                Dim lineElem
                For Each lineElem In lineElems
                    If Not IsObject(lineElem) Then
                    Else
                        ' flatten line object
                        Dim lineElemFlat
                        Set lineElemFlat = jsonExt.flatten(lineElem)
                        ' join row and line data into result row
                        Dim resultRow
                        Set resultRow = New Dictionary
                        jsonExt.joinDicts resultRow, dataRowFlat
                        jsonExt.joinDicts resultRow, lineElemFlat
                        ' push to result array
                        jsonExt.pushItem resultRows, resultRow
                    End If
                Next
            End If
        End If
    Next
    If UBound(resultRows) < 0 Then Exit Sub
    Output ThisWorkbook.Sheets(1), resultRows

End Sub

example.zip

[cityba]

@omegastripes super ,thank you very much, going nice 👍 🥇

[cityba]

@omegastripes did it even occur to me that maybe the order of the columns could be fixed somehow?

[omegastripes]

You may set the order you want in jsonExt.toArray() 3rd argument as array of strings with column names.

[cityba]

@omegastripes did you mean here and where do I put it in the code? to the end?

                    jsonExt.toArray ("invoiceNumber")  

[omegastripes]

Set breakpoint at the line with Output call. Step into and find toArray call. Make it jsonExt.toArray. There are 3 arguments. Pass the array with columns names in order you want as 3rd argument. Read notes in jsonExt.toArray, which arguments used as input and output. Create array like this header = Array("columnName1", "columnName2", "columnName3").

[cityba]

@omegastripes Type mismatch error, what do I mess up? Dim Head As Object Head = Array("invoiceNumber") jsonExt.toArray dataRow, resultRows, Head, False Output ThisWorkbook.Sheets(1), resultRows

[omegastripes]

Dim head() but not Dim head As Object. Rewrite the code from Output and remove call.

[cityba]

@omegastripes the resultRows "Type mismatch error, array or user defin.."

[omegastripes]

Share your code please

[cityba]

@omegastripes `

...If UBound(resultRows) < 0 Then Exit Sub Dim Head() Head = Array("invoiceIssueDate") jsonExt.toArray dataRow, resultRows, Head, False
Output ThisWorkbook.Sheets("szamla"), resultRows...`

[omegastripes]

1. Remove the line with Output call from your sub. Currently Output sub outputs data with no columns order set. That's why it doesn't fit your needs. Do not use it as is, rewrite it.
2. Inspect jsonExt.toArray(). Read notes in jsonExt.toArray() to understand which arguments used as input and output. In the code you shared you pass resulting data as 2nd argument, but it's incorrect sunce this argument is for output.
3. Find Output sub, inspect the code - use it as example, you may copy the code from this sub to your, change JSON.toArray to jsonExt.toArray, modify it so that the array with column names passed as 3rd arg. Take care to declare variables so that their types match jsonExt.toArray().

Let me know if any step is unclear to you.

[cityba]

@omegastripes That's how it goes now. Will it be good that way?

`Dim aHeader() Dim aData()

aHeader = Array("invoiceIssueDate", "invoiceMain.invoice.invoiceHead.supplierInfo.supplierTaxNumber.taxpayerId")
     jsonExt.toArray resultRows, aData, aHeader

     With ThisWorkbook.Sheets("szamla")
    .Activate
    .Cells.Delete
    With .Cells(1, 1)
        .Resize(1, UBound(aHeader) - LBound(aHeader) + 1).Value = aHeader
        .Offset(1, 0).Resize( _
                UBound(aData, 1) - LBound(aData, 1) + 1, _
                UBound(aData, 2) - LBound(aData, 2) + 1 _
            ).Value = aData
    End With
    .Columns.AutoFit
End With`

[cityba]

@omegastripes I have another question, can the header names be renamed to look nicer in the table? so invoiceNumber-> Acc. Number

[omegastripes]

Change the values you need in aHeader array right after the line with jsonExt.toArray call.

[cityba]

@omegastripes Is that it? Oh, I tested it with a lot of data today, and this version is 100% fast and stable, congratulations and thanks for a lot of help.

`jsonExt.toArray resultRows, aData, aHeader

aHeader = Array("Acc Number ", "Date") `

[cityba]

@omegastripes Hello, Another problem has arisen, it is rare to have such an account, which has a different route from invoiceMain. How can I list them if there are any? is there a solution for this?

invoiceMain.batchInvoice[0].invoice.invoiceHead.supplierInfo.supplierName sample1.txt

[omegastripes]

Check if batchInvoice exists, if not then wrap single invoice object into array and into dict with batchInvoice property. Make one more loops nest level for process invoices in batch.

[cityba]

@omegastripes did you think so? ` Dim lineArrPath lineArrPath = ".invoiceMain.invoice.invoiceLines.line" ' ".invoiceMain.invoice.invoiceLines.line" Dim lineArrBatch lineArrBatch = ".invoiceMain.batchInvoice" Dim dataRow

For Each dataRow In data("rows")
    ' retrieve line array from row object

    Dim lineElems, lineElemsExists, lineElemsb, lineElemsExistsb

    jsonExt.selectElement dataRow, lineArrPath, lineElems, lineElemsExists
     jsonExt.selectElement dataRow, lineArrBatch, lineElemsb, lineElemsExistsb

   If lineElemsExists Or lineElemsExistsb Then
        ' wrap single element into array to keep consistence
        If lineElemsExists And IsObject(lineElems) Then
            lineElems = Array(lineElems)

        End If
         If lineElemsExistsb And IsObject(lineElemsb) Then
           lineElemsb = Array(lineElemsb)

        End If

        If jsonExt.safeUBound(lineElems) > -1 Or jsonExt.safeUBound(lineElemsb) > -1 Then
       Dim lineElemsc     ' remove line array from row object
        If jsonExt.safeUBound(lineElemsb) > -1 Then
       dataRow("invoiceMain").Remove "batchInvoice"
       lineElemsc = lineElemsb
       End If
       If jsonExt.safeUBound(lineElems) > -1 Then
       dataRow("invoiceMain")("invoice")("invoiceLines").Remove "line"
       lineElemsc = lineElems
       End If                
            ' flatten the rest of row object
            Dim dataRowFlat
            Set dataRowFlat = jsonExt.flatten(dataRow)
            ' unpivot line array
            Dim lineElem

            For Each lineElem In lineElemsc
                If Not IsObject(lineElem) Then
                Else
                    ' flatten line object
                    Dim lineElemFlat
                    Set lineElemFlat = jsonExt.flatten(lineElem)
                    ' join row and line data into result row
                    Dim resultRow
                    Set resultRow = New Dictionary

                    jsonExt.joinDicts resultRow, dataRowFlat
                    jsonExt.joinDicts resultRow, lineElemFlat
                    ' push to result array

                    jsonExt.pushItem resultRows, resultRow

                End If
            Next                
        End If
    End If        
Next
`

[omegastripes]

Make one more loops nest level for process invoices in batch. Place batch check prior this loop.

[cityba]

@omegastripes I don't quite understand that. The only problem is that you don't put the data in one column because it subtracts invoiceMain from batchInvoice. do you have any idea for this?

[omegastripes]

Try this one

Sub testSample2()

    Dim sample
    sample = loadTextFromFile(ThisWorkbook.path & "\sample2a.json", "utf-8")
    Dim data
    Dim state As String
    JSON.Parse sample, data, state
    If state <> "Object" Then Exit Sub
    If Not IsArray(data("rows")) Then Exit Sub
    ' first pass to get rid of batch arrays
    Dim unbatchedRows
    unbatchedRows = Array()
    Dim dataRow
    For Each dataRow In data("rows")
        If IsObject(dataRow("invoiceMain")) Then
            ' copy invoiceMain to variable
            Dim invoiceMain
            Set invoiceMain = dataRow("invoiceMain")
            ' remove invoiceMain from row
            dataRow.Remove "invoiceMain"
            ' check if batch array exists
            Dim batchInvoice
            batchInvoice = Array()
            If IsArray(invoiceMain("batchInvoice")) Then
                batchInvoice = invoiceMain("batchInvoice")
                ' iterate each batch element
                Dim batchElem
                For Each batchElem In batchInvoice
                    ' create unbatched row
                    Dim unbatchedRow
                    Set unbatchedRow = New Dictionary
                    ' insert out-of-batch dataRow data into row
                    jsonExt.joinDicts unbatchedRow, dataRow
                    ' insert in-batch invoice data into row
                    jsonExt.joinDicts unbatchedRow, batchElem("invoice")
                    ' push unbatched row to array
                    jsonExt.pushItem unbatchedRows, unbatchedRow
                Next
            Else
                If IsObject(invoiceMain("invoice")) Then
                    ' insert invoice data into row
                    jsonExt.joinDicts dataRow, invoiceMain("invoice")
                    ' push row to array
                    jsonExt.pushItem unbatchedRows, dataRow
                End If
            End If
        End If
    Next
    If UBound(unbatchedRows) < 0 Then Exit Sub
    '    outputJson unbatchedRows, ThisWorkbook.Sheets(1)
    '    saveTextToFile JSON.Serialize(unbatchedRows), ThisWorkbook.path & "\unbatchedRows.json", "utf-8"
    ' second pass to get rid of line arrays
    Dim lineArrPath
    lineArrPath = ".invoiceLines.line"
    Dim resultRows
    resultRows = Array()
    For Each unbatchedRow In unbatchedRows
        ' retrieve line array from row object
        Dim lineElems, lineElemsExists
        jsonExt.selectElement unbatchedRow, lineArrPath, lineElems, lineElemsExists
        If lineElemsExists Then
            ' wrap single element into array to keep consistence
            If IsObject(lineElems) Then
                lineElems = Array(lineElems)
            End If
            If jsonExt.safeUBound(lineElems) > -1 Then
                ' remove line array from row object
                unbatchedRow("invoiceLines").Remove "line"
                ' flatten the rest of row object
                Dim dataRowFlat
                Set dataRowFlat = jsonExt.flatten(unbatchedRow)
                ' unpivot line array
                Dim lineElem
                For Each lineElem In lineElems
                    If IsObject(lineElem) Then
                        ' flatten line object
                        Dim lineElemFlat
                        Set lineElemFlat = jsonExt.flatten(lineElem)
                        ' join row and line data into result row
                        Dim resultRow
                        Set resultRow = New Dictionary
                        jsonExt.joinDicts resultRow, dataRowFlat
                        jsonExt.joinDicts resultRow, lineElemFlat
                        ' push to result array
                        jsonExt.pushItem resultRows, resultRow
                    End If
                Next
            End If
        End If
    Next
    ' output
    If UBound(resultRows) < 0 Then Exit Sub
    outputJson resultRows, ThisWorkbook.Sheets(1)
    MsgBox "Completed"

End Sub

example.zip

[cityba]

@omegastripes super, i thought it would be easier but ignoring it is not. thank you very much for all the help.

[cityba]

@omegastripes I have another question, can I load 2-3 data in one cell at a time? for example: ' (...taxpayerId +...vatCode)' one cells value. can it be fixed?

[omegastripes]

You may process the result array prior to passing it toArray(), concatenate values from certain fields, remove those fields from dict and add new field with concatenated string to dict, or simply change some fields values.

[cityba]

@omegastripes Hi thank you for all the help.