Mulesoft dataweave outer join

Question

Need your help in transform a message like below using DataWeave 2.0.

If I have the following JSON payload of users & order. The output needed is the list of users who have orders (inner join) & the users who does not have orders. The list should not contain the users data if they have the orders which is not present in order list.

I tried with outerjoin from dataweave but that will pull the users who have an order but not present in orders list as well.

Please find the example below.

var users = [
{
  "id": "1",
  "name": "Jerney",
  "order_id": "101",
},
{
  "id": "2",
  "name": "Marty"
  "order_id": "102",
},
{
  "id": "3",
  "name": "Marty"
}
]


var orders = [
{
   "order_id": "101",
   "product": "bread"
},
{
   "order_id": "104",
   "product": "broccoli"
}

]

The output needed is

[
{
  "id": "1",
  "name": "Jerney",
  "order_id": "101",
  "product": "bread"
}
{
  "id": "3",
  "name": "Marty"
}
]

I hope example clears the expected output. I tried filtering (like outer join) but it will include the order id 102 as well in the input.

Thanks for your help!

question from:https://stackoverflow.com/questions/65859437/mulesoft-dataweave-outer-join

Answer 1

I found a way to achieve that but I had some strange issue in my test with the missing an order_id in the join, even after filtering it. I used an extra map to workaround. It should not be needed. This script returns exactly your desired output.

%dw 2.0
output application/json
import * from dw::core::Arrays
var users = [
    {
        "id": "1",
        "name": "Jerney",
        "order_id": "101"
    },
    {
        "id": "2",
        "name": "Marty",
        "order_id": "102"
    },
    {
        "id": "3",
        "name": "Marty"
    } 
]

var orders = [
    {
        "order_id": "101",
        "product": "bread"
    },
    {
        "order_id": "104",
        "product": "broccoli"
    }
]

fun getUserWithOrders(users) = (users filter $.order_id?) 
        map ($ mapObject ($$):$) // for some reason had to add this to avoid a null error in the join

fun getUserWithoutOrders(users) = (users filter not $.order_id?) 
---
join(getUserWithOrders(users), orders, (u) -> u.order_id, (o) -> o.order_id)
    map ($.l ++ {product: $.r.product})
    ++ getUserWithoutOrders(users)