How to combine 2 json payload in mule 4 using dataweave

Question

From two different flows I am getting a json output payload and what to combine them based on a common key. The keys are dynamic, so I want to write a generic dataweave that will work for any key.

Input-1:

[
  {
    "CustomerID": "123",
    "Name": "Maria",
    "Phone": "030-0074321"
    
  },
  {
    "CustomerID": "56654",
    "Name": "sdf",
    "Phone": "030-6877452"
    
  }
]

Input-2:

[
    {
      "OrderID": "10643",
      "CustomerID": "123",
      "Price": "200"
    },
    {
      "OrderID": "10692",
      "CustomerID": "123",
      "Price": "566"
    },
    {
      "OrderID": "10702",
      "CustomerID": "56654",
      "Price": "546"
    }
  ]

Expected Output:

[
   {
      "CustomerID":"123",
      "Name":"Maria",
      "Phone":"030-0074321",
      "Details":[
         {
            "OrderID":"10643",
            "Price":"200"
         },
         {
            "OrderID":"10692",
            "Price":"566"
         }
      ]
   },
   {
      "CustomerID":"56654",
      "Name":"sdf",
      "Phone":"030-6877452",
      "Details":[
         {
            "OrderID":"10702",
            "Price":"546"
         }
      ]
   }
]

Based on the common key(in this example CustomerID) I want to combine both the inputs. As I mentioned all the keys (CustomerID,Name,Phone,OrderID,Price) are not same all the time, they are dynamic.

Can someone help me write a dynamic dataweave code?

Thanks in advance

Answer 1

Here's something quick I came up with:

%dw 2.0
output application/dw

var input1 = [
  {
    "CustomerID": "123",
    "Name": "Maria",
    "Phone": "030-0074321"
    
  },
  {
    "CustomerID": "56654",
    "Name": "sdf",
    "Phone": "030-6877452"
    
  }
]

var input2 = [
    {
      "OrderID": "10643",
      "CustomerID": "123",
      "Price": "200"
    },
    {
      "OrderID": "10692",
      "CustomerID": "123",
      "Price": "566"
    },
    {
      "OrderID": "10702",
      "CustomerID": "56654",
      "Price": "546"
    }
  ]

fun combineByKey(in1,in2,k) = do {
    var groupedBy = in2 groupBy $[k]
    ---
    in1 map {
        ($),
        details: groupedBy[$[k]]
    }
}


---

combineByKey(input1,input2,"CustomerID")

//do {
//  var groupedBy = input2 groupBy $.CustomerID
//  ---
//  input1 map {
//      ($),
//      details: groupedBy[$.CustomerID]
//  }
//}

As you can see from the commented out expression at the bottom it is not that long so I don't think you need a function IMHO.

In essence you only need to know two functions groupBy and map, then how to create closures (aka localized declarations) by using do {}, and finally how to access fields dynamically.

I bet if I spend a little more time I should be able to come up with a better function, but this will do for now :)

Potentially there is some builtin function that does it already but I don't know of one. Maybe someone will point it out.

Answer 2

Maybe you can make finding the CommonKey as dynamic as well. I know this might be an overkill but i am leaving it out here in case it helps in any ways possible.

%dw 2.0
output application/json
var inp1= [
  {
    "CustomerID": "123",
    "Name": "Maria",
    "Phone": "030-0074321"
  },
  {
    "CustomerID": "56654",
    "Name": "sdf",
    "Phone": "030-6877452"
  }
]

var inp2= [
    {
      "OrderID": "10643",
      "CustomerID": "123",
      "Price": "200"
    },
    {
      "OrderID": "10692",
      "CustomerID": "123",
      "Price": "566"
    },
    {
      "OrderID": "10702",
      "CustomerID": "56654",
      "Price": "546"
    }
  ]

  var OrderIdCommon = inp1 map (firstInputValue) -> using (id = firstInputValue.OrderID) {
     (inp2 filter ($.*OrderID contains id)  map (secondInputValue) -> {
      OrderId : secondInputValue.OrderID 
    } ) 
  }

   var CustomerIdCommon = inp1 map (firstInputValue) -> using (id = firstInputValue.CustomerID) {
     (inp2 filter ($.*CustomerID contains id)  map (secondInputValue) -> {
      CustomerID: secondInputValue.CustomerID
    } ) 
  }

  var CommonKey = ((if(sizeOf(OrderIdCommon - {}) > 0) OrderIdCommon else CustomerIdCommon)[0] pluck $$)[0]

  var inp1HasCKey = inp1[0] pluck $$ contains CommonKey
  var inp2HasCKey = inp2[0] pluck $$ contains CommonKey

fun combineByKey(in1,in2,k) = do {
    var groupedBy = (in2 groupBy $[k])
    ---
    in1 map {
        ($),
        details: groupedBy[$[k]] map {
            a: (($) - k)
        }.a
    } 
}

---

(if((inp1HasCKey == true) and (inp2HasCKey == true))
{
    a: combineByKey(inp1,inp2,CommonKey as String)
}
else
{
    a: "No common key found"
}).a

Answer 3

This dataweave transformation is fulfilling the criteria that you mentioned.

   

    %dw 2.0
    output application/json
    ---
    input1 map(value) -> using (id = value.CustomerID)
    {
       CustomerID: value.CustomerID,
       Name: value.Name,
       Phone:value.Phone,
       Details: (input2 filter ($.*'CustomerID' contains  id) map ($ mapObject (k,v) ->{
         (v):k
         } - "CustomerID"))
    }

Answer 4

You can achieve it by using some recursive or just a multiple function by getting keys then comparing it between in1 and in2 and then restructure to create a foreignKey. The trick was creating a primaryKey which is the value of all the commonKey.

Try the script below. The in1 is like the Primary reference and in2 is a Secondary reference. To test it, try adding a Name field in the in2 and use at least a match value Name and CustomerID from in1

%dw 2.0
var in1=[
{
  "CustomerID": "123",
  "Name": "Maria",
  "Phone": "030-0074321"
},
{
  "CustomerID": "56654",
  "Name": "sdf",
  "Phone": "030-6877452"
}
]
var in2=[
  {
    "OrderID": "10643",
    "CustomerID": "123",
    "Price": "200"
  },
  {
    "OrderID": "10692",
    "CustomerID": "123",
    "Price": "566"
  },
  {
    "OrderID": "10702",
    "CustomerID": "56654",
    "Price": "546"
  }
]
fun getCommonKey(key1, key2) = key1 -- (key1 -- key2)
var commonKey = getCommonKey(flatten(in1 map keysOf($)) distinctBy $, flatten(in2 map keysOf($)) distinctBy $)
fun getPrimaryKey(v)=(commonKey map (v1,i1) -> {
          name:v1,value: v[(v1)]
  }.value) joinBy "-"
output application/json
---
in1 map (v,i) ->{
  (v),
  (using (pkId=getPrimaryKey(v), in2WithForeignKey = in2 map (v2,i2) ->
      {
        foreignKey: (commonKey map (v1,i1) -> {
            name:v1,
            value: v2[(v1)] default ""
        }.value )  joinBy "-",
        (v2)
      }
      ){
        (using (details = ((in2WithForeignKey[?($.foreignKey == pkId)] default [])
            map (($ -- (commonKey)) - "foreignKey" ))) {
            (Details: details) if (!isEmpty(details))
      })
  })
}

Answer 5

Here's another solution with the common key being dynamic:

%dw 2.0
output application/json
import * from dw::core::Arrays
var inp1=[
  {
    "CustomerID": "123",
    "Name": "Maria",
    "Phone": "030-0074321"
    
  },
  {
    "CustomerID": "56654",
    "Name": "sdf",
    "Phone": "030-6877452"
    
  }
]
var inp2=[
    {
      "OrderID": "10643",
      "CustomerID": "123",
      "Price": "200"
    },
    {
      "OrderID": "10692",
      "CustomerID": "123",
      "Price": "566"
    },
    {
      "OrderID": "10702",
      "CustomerID": "56654",
      "Price": "546"
    }
  ]

//get all the keys from both the arrays
var inp1keys=((inp1 reduce(item,acc) -> item ++ acc) pluck $$) distinctBy $
var inp2keys=((inp2 reduce(item,acc) -> item ++ acc) pluck $$) distinctBy $
//get the matching key in the array
var matchingkey=((inp1keys  map (v0,k0) ->
{
    matched: if(inp2keys contains v0) v0 else null
}.matched) filter $ != null)[0]
---
/*
Steps for the script below:
1.Join both the array on the common key fetched dynamically above.
2.Remove the common key from the rigt part obtained after join.
3.After that merge the Details part for a given common key under a given common key id(achieved with the reduce)
*/
((join(inp1, inp2, (inp1) -> inp1."$(matchingkey)", (inp2) -> inp2."$(matchingkey)")) 
map (v0,k0) ->
{
    ((v0.l) ++ (Details:v0.r - matchingkey))
}) reduce (item,acc) ->  if( acc."$(matchingkey)" contains item."$(matchingkey)"[0])  
((acc - 'Details') ++ Details:[acc.Details , item.Details])
else [acc] + item

Anyways from a performance perspective, tread carefully with scripts like these.