Getting more then just the first item in JSON array using CHOETL converting JSON to CSV

Question

Been struggling with the following: I have a JSON response of orders from our Ecommerce website (Shopify). I need to create a CSV from the response. Everything is fine for me until I get to the line item details. I only get the first item in the array. I have seen other solutions that showed the other array items as additional columns however I need to see these as rows. A lot of examples I have seen are also in C# which I am not great with.

Order Class

Imports ChoETL

Public Class Order
    <ChoJSONRecordField>
    Public Property Name As String
    <ChoJSONRecordField>
    Public Property Email As String
    <ChoJSONRecordField(JSONPath:="financial_status")>
    Public Property Financial_Status As String
    <ChoJSONRecordField(JSONPath:="line_items[*].title")>
    Public Property Title As String

End Class

Create CSV sub

Private Shared Sub UsingPOCO()
    Using csv = New ChoCSVWriter("order3.csv").WithFirstLineHeader()

        Using json = New ChoJSONReader(Of Order)("order2.json")
            csv.Write(json)
        End Using
    End Using
End Sub

Sample JSON

{
  "email": "[email protected]",
  "financial_status": "paid",
  "name": "#CCC94440",
  "line_items": [
    {
      "title": "product1",
      "quantity": 3
    },
    {
      "title": "product2",
      "quantity": 2
    },
    {
      "title": "product3",
      "quantity": 1
    }
  ]
}

CSV Output

What I need

OR

Update #1 I have found this answer on another question that seems to be on the track I want. However I can't figure out how to convert it to VB.net. The answer I believe will work is the selected answer update #2. https://stackoverflow.com/a/57166153/2037475

Update #2 I was able to convert the C# from the other answer to VB.net.... However I get the following error which I am still looking into: "'Select' is not a member of 'Dynamic()'"

    Using fw = New StreamWriter("order3.csv", True)

        Using w = New ChoCSVWriter(fw).WithFirstLineHeader()

            Using r = New ChoJSONReader("order2.json").WithJSONPath("$.line_items[*]")
                w.Write(r.SelectMany(Function(r1) (CType(r1.line_items, Dynamic())).[Select](Function(r2) New With {r1.name, r2.title})))
            End Using
        End Using
    End Using

    Console.WriteLine(File.ReadAllText("order3.csv"))

Update 3

I dont need to stick with CHOETL its just the first thing I found that I had success with. Open to any suggestions.

Thanks, Matt

Answer 1

Here is working sample of it in VB.NET

        Dim json As String

        json = "
{
  ""email"": ""[email protected]"",
  ""financial_status"": ""paid"",
  ""name"": ""#CCC94440"",
  ""line_items"": [
    {
      ""title"": ""product1"",
      ""quantity"": 3
    },
    {
      ""title"": ""product2"",
      ""quantity"": 2
    },
    {
      ""title"": ""product3"",
      ""quantity"": 1
    }
  ]
}"
        Dim csv As New StringBuilder

        Using w = New ChoCSVWriter(csv).WithFirstLineHeader()
            Using r = ChoJSONReader.LoadText(json)
                w.Write(r.SelectMany(Function(r1) (CType(r1.line_items, Object())).[Select](Function(r2) New With {r1.email, r1.financial_status, r1.name, r2.title, r2.quantity})))
            End Using
        End Using

        Console.WriteLine(csv.ToString())

Output:

email,financial_status,name,title,quantity
[email protected],paid,#CCC94440,product1,3
[email protected],paid,#CCC94440,product2,2
[email protected],paid,#CCC94440,product3,1

UPDATE #1:

To retrieve price from shipping items

        json = "
{
  ""email"": ""[email protected]"",
  ""financial_status"": ""paid"",
  ""name"": ""#CCC94440"",
  ""line_items"": [
    {
      ""title"": ""item0"",
      ""quantity"": 3
    },
    {
      ""title"": ""item1"",
      ""quantity"": 2
    }
  ],
  ""shipping_lines"": [
    {
      ""title"": ""Free Shipping"",
      ""price"": ""1.00""
    }
  ]
}
"
        Dim csv As New StringBuilder

        Using w = New ChoCSVWriter(csv).WithFirstLineHeader()
            Using r = ChoJSONReader.LoadText(json)
                w.Write(r.SelectMany(Function(r1) CType(r1.line_items, Object()).[Select](Function(r2)
                                                                                              Return New With
                                                                                                {
                                                                                                    r1.email,
                                                                                                    r1.financial_status,
                                                                                                    r1.name,
                                                                                                    r2.title,
                                                                                                    r2.quantity,
                                                                                                    CType(r1.shipping_lines, Object())(0).price
                                                                                                }
                                                                                          End Function)))
            End Using
        End Using

        Console.WriteLine(csv.ToString())

Output:

email,financial_status,name,title,quantity,price
[email protected],paid,#CCC94440,item0,3,1.00
[email protected],paid,#CCC94440,item1,2,1.00

Answer 2

To be honest, with all your emerging requirements, it's going to get messier and messier to keep trying to ram all this into a single line of LINQ...

I'd head to jsonutils.com and paste the json there to turn it into VB, turn on PascalCase and JsonProperty attributes:

Public Class LineItem

    <JsonProperty("title")>
    Public Property Title As String

    <JsonProperty("quantity")>
    Public Property Quantity As Integer
End Class

Public Class ShippingLine

    <JsonProperty("title")>
    Public Property Title As String

    <JsonProperty("price")>
    Public Property Price As String
End Class

Public Class Example

    <JsonProperty("email")>
    Public Property Email As String

    <JsonProperty("financial_status")>
    Public Property FinancialStatus As String

    <JsonProperty("name")>
    Public Property Name As String

    <JsonProperty("line_items")>
    Public Property LineItems As LineItem()

    <JsonProperty("shipping_lines")>
    Public Property ShippingLines As ShippingLine()
End Class

Then some loops are a tad more readable:

'inside Using w....

Dim root = NewtonSoft.Json.JsonConvert.DeserializeObject(your_json_string)

For Each li in root.LineItems

    Dim csvLineObject = New With { _
        root.email, _
        root.financial_status, _
        root.name, _
        li.title, _
        li.quantity, _
        root.ShippingLines.First().Price, _
        root.OtherArrayMightBeEmpty.FirstOrDefault()?.OtherProperty
    }
    w.Write(csvLineObject)

Next li

Never used CHOETL, can probably get away with a similar construct on r (= root here)