Dataweave - how to group by and sum details form array of elements

Question

hope you are well, how can I generate dynamic arrays by state? That is, maybe the payload in this case has two types of state (hold, fail) but in another case maybe it has different I don't know and the output payload needs to check the state in the input group by state, remove the fields you don't need and create a total field per state and add those that belong to it...

Input:

[
 {
   "numorder": "470",
   "message": "Order number 470 already registered.",
   "status": "hold",
   "description":"",
   "createAt":""
 },
 {
   "numorder": "471",
   "message": "Order number 471 already registered.",
   "status": "failure",
   "description":"",
   "createAt":""
 },{
   "numorder": "472",
   "message": "Order number 472 already registered.",
   "status": "failure",
   "description":"",
   "createAt":""
 }
]

Output:

{
  "totalHold":1,
  "totalFailure":2,
  "items":[
     { 
        "hold":[
           {
              "numorder":"470",
              "message":"Order number 470 already registered."
           }
        ],
        "failure":[
           {
              "numorder":"471",
              "message":"Order number 471 already registered."
           },
           {
              "numorder":"472",
              "message":"Order number 472 already registered."
           }
        ]
     }
  ]

}

NOTE: the state has to be dynamic because I don't know if the input is failure, hold, success, etc.

Any help would be appreciated. Thank you.

Answer 1

Updated to make it dynamic:

%dw 2.0
import capitalize from dw::core::Strings
output application/json  
---
do {
  var grpSts = payload groupBy $.status
  var keys = grpSts pluck $$
  var values = grpSts pluck $
  ---
  ((
      (keys map 
     ("total" ++ capitalize($)) : sizeOf(grpSts[$]
     )
  )) reduce ((item, acc = {}) ->  acc ++ item)) ++ (
    items: grpSts pluck 
      ($$): ($ map ($ - 'status' - 'description' - 'createAt'))

  )
}
    

Answer 2

Dynamic? No se hable más...

First of all, you need to group by status and then count all the elements for every group found, this will be the base for the complete solution

var results = 
        payload groupBy ((item, index) -> item.status)
        pluck ((value, key, index) -> 
            (key as String): sizeOf(value)
        )

This will give you the next result:

[
  {
    "hold": 2
  },
  {
    "failure": 2
  },
  {
    "success": 1
  }
]

Then, you need to convert the array of objects of status to a single object where every element is the status found:

{(
    results map ((item, index) -> 
        item mapObject ((total, status, index) -> 
            (status): total
        )
    ) 
)}

This will give you next result:

{
  "hold": 2,
  "failure": 2,
  "success": 1
}

Next, based on the first result array, iterate over every status and filter the payload, then print only the numorder and message attribute

{ 
  "items":
    results map ((item, index) -> 
        item mapObject ((total, status, index) -> 
            (status as String): 
                (payload filter ((itemPayload, indexPayload) -> 
                    itemPayload.status == (status as String))
                        map ((item, index) -> { 
                            "numorder": item.numorder,
                            "message": item.message
                        })
                )
            )
        )
}

In order to test the results, I added an extra 2 elements with a different status and with and different order (not together)

[
 {
   "numorder": "470",
   "message": "Order number 470 already registered.",
   "status": "hold",
   "description":"",
   "createAt":""
 },
 {
   "numorder": "471",
   "message": "Order number 471 already registered.",
   "status": "failure",
   "description":"",
   "createAt":""
 },
 {
   "numorder": "472",
   "message": "Order number 472 already registered.",
   "status": "failure",
   "description":"",
   "createAt":""
 },
 {
   "numorder": "473",
   "message": "Order number 473 already registered.",
   "status": "success",
   "description":"",
   "createAt":""
 },
  {
   "numorder": "474",
   "message": "Order number 474 already registered.",
   "status": "hold",
   "description":"",
   "createAt":""
 } 
]

The final result is:

{
  "hold": 2,
  "failure": 2,
  "success": 1,
  "items": [
    {
      "hold": [
        {
          "numorder": "470",
          "message": "Order number 470 already registered."
        },
        {
          "numorder": "474",
          "message": "Order number 474 already registered."
        }
      ]
    },
    {
      "failure": [
        {
          "numorder": "471",
          "message": "Order number 471 already registered."
        },
        {
          "numorder": "472",
          "message": "Order number 472 already registered."
        }
      ]
    },
    {
      "success": [
        {
          "numorder": "473",
          "message": "Order number 473 already registered."
        }
      ]
    }
  ]
}

The complete code:

%dw 2.0
output application/json
var results = 
        payload groupBy ((item, index) -> item.status)
        pluck ((value, key, index) -> 
            (key as String): sizeOf(value)
        )
---
{(
    results map ((item, index) -> 
        item mapObject ((total, status, index) -> 
            (status): total
        )
    ) 
)}
++
{ 
  "items":
    results map ((item, index) -> 
        item mapObject ((total, status, index) -> 
            (status as String): 
                (payload filter ((itemPayload, indexPayload) -> 
                    itemPayload.status == (status as String))
                        map ((item, index) -> { 
                            "numorder": item.numorder,
                            "message": item.message
                        })
                )
            )
        )
}

Answer 3

Keeping the status calculation dynamic whereby it could be any value. This capitalizes hold and failure in the output.

%dw 2.0
output application/json
import * from dw::core::Strings
var outputKeys = payload groupBy ((item, index) -> item.status ) mapObject {
    ($$) :  sizeOf($)
}
---
(payload groupBy $.status mapObject {
    ("total" ++ capitalize(((outputKeys pluck $$)[($$$)]))) : outputKeys[($$)]

})
++
(items: [payload groupBy $.status mapObject ((value, key, index) -> {
        (  ((outputKeys pluck $$)[(index)]) : value map (
             $ - 'status' - 'description' - 'createAt'
        ))
}
)])

Answer 4

The items in your expected output is essentially the output of the groupBy function with just a few fields. So you can use map after groupBy to get your required item list.

For the totalStatus part, I created a helper function capitalizeFirst to make it easy for capitalizing the first letter of the status. Putting it together it will look like this

%dw 2.0
output application/json

//This function takes a string and capitalizes the first letter of it. it will help to in the fields of totals i.e. "hold" to "totalHold"
fun capitalizeFirst(string: String) = upper(string[0]) ++ (string[1 to -1] default '')
//Simply a helper function to make code more readable. This is to basically keep the required fields only from paylaod.
fun getItemFields(fullItemArray) = fullItemArray map {
    numorder: $.numorder,
    message: $.message,
}

var groupedData = payload groupBy $.status
var statusTotals = groupedData mapObject ('total' ++ capitalizeFirst($$)): sizeOf($)
---
{
    (statusTotals),
    items: [ groupedData mapObject ($$): getItemFields($) ]
}