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

MatthewBrown
January 28, 2021
160 views
0 votes
2 Answers

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

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

Answers

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

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)

Please signup or login to give your own answer.

Click here to cancel reply.

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

Answers