Tabular Data to Nested JSON

Question

I am trying to convert tabular query output to nested JSON using MuleSoft. My query output is like below:

----------------------------------
Customer |  Order   |   Items
----------------------------------
C       |   Order1  |   Itm1
C       |   Order1  |   Itm2
C       |   Order2  |   Itm1
C       |   Order2  |   Itm4
C       |   Order3  |   Itm3
C       |   Order3  |   Itm4

and using Mule4 dataweave I am trying to convert it in flowing JSON output:

Customer: C
Orders: {
            Order1:{
                Items: {                  
                  Item: Item1
                  Item: Item2
                }
            }
            Order2:{
                Items: {                  
                  Item: Item1
                  Item: Item4
                }
            }

            Order3:         

}

So far I have tried code like below with no luck:

%dw 2.0
output application/json
---

    payload map ((st, stindex) -> {
        Customer:   st.Customer,

        Orders: payload filter(($.Customer == st.Customer) and  ($.Order == st.Order)) map ((f, fIndex) ->{

            Order: f.Order      

            Items : payload filter (($.ItemName == f.ItemName) and ($.Order == f.Order)) map ((i, iIndex) -> {

                item: i.ItemName
            })

        })

    })

it seems I am missing something important. Appreciate your help!!

Answer 1

I needed a multilevel groupBy(), mapObjects and a reduce() for the item.

%dw 2.0
output application/json
---
(payload map {
    Customer: $.Customer,
    Order: $.Order,
    Items: $.Items
} groupBy (item) -> item.Customer) 
    mapObject ((value, key, index) -> { 
        Customer : key, 
        Orders: (value groupBy (orders)->orders.Order) 
            mapObject ((value1, key1,  index1) -> {
                    "$(key1)": value1 reduce ((item, accumulator={}) -> accumulator ++ {Item: item.Items} )
            }     
            )
        }
      )

Output:

{
  "Customer": "C",
  "Orders": {
    "Order2": {
      "Item": "Itm1",
      "Item": "Itm4"
    },
    "Order1": {
      "Item": "Itm1",
      "Item": "Itm2"
    },
    "Order3": {
      "Item": "Itm3",
      "Item": "Itm4"
    }
  }
}

The indenting of the script is bad but it is too late for me to fix it.