CODEWITHSUNDEEP
muledataweavemulesoftmule4
poppp
poppp

Reputation: 341

Sum with GroupBy in Nested Arrays in dataweave 2, Mule 4

I would like to group by using the join_date and get the average qty (formulated by summing all in qty then divided by the number of records that have qty; do not take into account for those without qty).

Input

{
    "number": 282,
    "topic": [
        {
            "fruit": "apple",
            "colour": "red",
            "join_date": "today",
            "quality": [
                {
                    "date": "2020-08-21",
                    "in": {
                        "feedback": "good",
                        "qty": "3 qty"
                    },
                    "out": {
                        "feedback": "poor",
                        "qty": "1 qty"
                    }
                },
                {
                    "date": "2020-08-21",
                   "in" :{}
                },

                {
                    "date": "2020-08-22",
                    "in": {
                        "feedback": "normal",
                        "qty": "3 qty"
                    }
                } 

]},
{
            "fruit": "banana",
            "colour": "yellow",
            "join_date": "Yesterday",
            "quality": []
} ] }

Below is an example of intended output:

Output

number, fruit, colour, join_date, avginqty, avgoutqty
282, apple, red, today,  3 qty, 1 qty
282, banana, yellow, yesterday, ,

Thank you.

Upvotes: 0

Views: 465

Answers (2)

Rajhans Jakhmola
Rajhans Jakhmola

Reputation: 11

Use groupBy function to group the records and filter to ignore all the qty less than zero.

filter

groupBy

The basic transformation is below, Please add groupBy and filter accordingly -

    %dw 2.0
output application/csv
---
payload.topic[0].quality map ({
     number: payload.number,
     fruit: payload.topic[0].fruit,
     colour: payload.topic[0].colour,
     date:$."date",
     feedback_in: $.in.feedback,
     qty_in: $.in.qty,
     feedback_out: $.out.feedback,
     qty_out: $.out.qty
     })

Upvotes: 0

Salim Khan
Salim Khan

Reputation: 4303

To the previous question that you had asked, there were a lot of ways in which the problem was solved which should have given you a fair enough idea to get started on how to attack the problem. With your question, input and expected output please do post in your attempt (DW script) at solving the problem and as to where you are stuck with the same.

You can also make use of the playground that comes along with tutorial to help you understand the usage in a better way.

Script

%dw 2.0
output application/csv
import * from dw::core::Arrays
---
flatten(payload.topic  groupBy $.join_date mapObject ((item) -> {
     temp: (if(sizeOf(item.quality)==0) [{}] else item.quality) default [{}] map {
     number: payload."number",
     fruit: item.fruit[0],
     colour: item.colour[0],    
     join_date: item."join_date"[0] default "",
     avginqty: ((($.in..qty) sumBy ($ splitBy " ")[0])/(($.in..qty) countBy ($ splitBy " ")[0] as Number  > -1)) ++ " qty" default " ",
     avgoutqty: ((($.out..qty) sumBy ($ splitBy " ")[0])/(($.out..qty) countBy ($ splitBy " ")[0] as Number  > -1)) ++ " qty" default " ",
     }
}) pluck $)

Output

number,fruit,colour,join_date,avginqty,avgoutqty
282,apple,red,today,3 qty,1 qty
282,banana,yellow,Yesterday, ,

Upvotes: 1

Related Questions