dataweave - How to join the list of sku with same order in one object in the array

Question

How to join the list of sku with same order in one object in the array.

Input:

[{
 "number": "7358",
 "sku": "301-01",
 "desc": "1"
}, {
 "number": "7358",
 "sku": "301-02",
 "desc": "2"
}, {
 "number": "7359",
 "sku": "301-03",
 "desc": "3"
}, {
 "number": "7359",
 "sku": "301-03",
 "desc": "4"
}, {
 "number": "7360",
 "sku": "301-03",
 "desc": "5"
}]

Output:

[{
  "number": "7358",
  "list": [{
      "sku": "301-01",
      "desc": "1"
  }, {
      "sku": "301-02",
      "desc": "2"
  }]
}, {
  "number": "7359",
  "list": [{
      "sku": "301-03",
      "desc": "3"
  }, {
      "sku": "301-03",
      "desc": "4"
  }]
}, {
  "number": "7360",
  "list": [{
      "sku": "301-03",
      "desc": "5"
  }]
}]

Output we just create a new "list" array that contains the skus and the desc that have the order in common. Any help would be appreciated. Thank you.

What do you mean by order in common ? Please be descriptive in your ask. — Salim Khan, Apr 28 '22 at 07:39

score 2 · Accepted Answer · answered Apr 28 '22 at 14:31

Hi The best way as shown by Salim is the groupBy I did a few modifications

%dw 2.0
output application/json
---
payload 
    groupBy ((item, index) -> item.number)
    mapObject ((value, key, index) -> 
        {
            number: key,
            list: value map ((item, index) -> item - "number") //We remove the number field
        }
    )

Simple the key of the groupBy is the criteria that was group so it is the number and the simplest way to re build the output structure is by doing the map and removing the "number" field

thanks for your help – Jesus Abraham Felix González Jun 12 '23 at 23:21 — Jesus Abraham Felix González, Jun 12 '23 at 23:21

Salim Khan · Answer 2 · 2022-04-28T17:02:58.363

Try this script:

%dw 2.0
output application/json
---
payload groupBy $.number mapObject ((value, key, index) -> {
           number: value.number[0],
           list: value reduce ((item1, a =[]) -> a + { sku: item1.sku ,  desc: item1.desc})
}
)

Here is the updated script that matches the output:

%dw 2.0
output application/json
---
payload groupBy $.number pluck $ map ((item,index) -> {
           number: item.number[0],
           list: item reduce ((item1, a =[]) -> a + { sku: item1.sku ,  desc: item1.desc})
}
)

sudhish_s · Answer 3 · 2022-04-28T18:21:25.767

As other experienced members have mentioned, groupBy is the right way to achieve this. However, I noticed there is a difference in the output with the given approaches. The following script will output an array of items.

Script is updated to add shippingDate based on the new changes provided in the comment. Assumption is shippingDate is same for an order (number).

%dw 2.0
output application/json
var itemByNumber = payload groupBy ((item) -> item.number)
var removeKeys = ["number", "shippingDate"]
---
keysOf(itemByNumber) map ((key) -> 
do  {
    var item = itemByNumber[key]
    ---
    {
        number: key,
        shippingDate: item[0].shippingDate,
        list: item map ($ -- removeKeys)
    }
})

Output

[
  {
    "number": "7358",
    "shippingDate": "28/04/2022",
    "list": [
      {
        "sku": "301-01",
        "desc": "1"
      },
      {
        "sku": "301-02",
        "desc": "2"
      }
    ]
  },
  {
    "number": "7359",
    "shippingDate": "29/04/2022",
    "list": [
      {
        "sku": "301-03",
        "desc": "3"
      },
      {
        "sku": "301-03",
        "desc": "4"
      }
    ]
  },
  {
    "number": "7360",
    "shippingDate": "27/04/2022",
    "list": [
      {
        "sku": "301-03",
        "desc": "5"
      }
    ]
  }
]

Input

[
  {
    "number": "7358",
    "shippingDate":"28/04/2022",
    "sku": "301-01",
    "desc": "1"
  },
  {
    "number": "7358",
    "shippingDate":"28/04/2022",
    "sku": "301-02",
    "desc": "2"
  },
  {
    "number": "7359",
    "shippingDate":"29/04/2022",
    "sku": "301-03",
    "desc": "3"
  },
  {
    "number": "7359",
    "shippingDate":"29/04/2022",
    "sku": "301-03",
    "desc": "4"
  },
  {
    "number": "7360",
    "shippingDate":"27/04/2022",
    "sku": "301-03",
    "desc": "5"
  }
]

hello this is the closest answer but i have a problem if i have another field "shippingDate" on the same line for number and sku and just put it in the number position how can i do it? Because the function is preparing for number as the main element. Sample: [{ "number": "7358", "shippingDate":"28/04/22022", "list": [{ "sku": "301-01", "desc": "1" }, { "sku": "301-02", "desc": "2" }] } — Jesus Abraham Felix González, Apr 28 '22 at 15:31
Please update the input. Can I assume Shipping Date will be same value for an order Number? — sudhish_s, Apr 28 '22 at 18:14

dataweave - How to join the list of sku with same order in one object in the array

3 Answers3