how to merge two Json based on key value in Mule

Question

Hi I have a 1 json as below

"first": [
{
  "projectid": "15",
  "approval_status": "A"
},
{
  "projectid": "24",
  "approval_status": "A"
}  ]}

The next payload is stores in a flowVariable

{
 "Second": [
{
  "projectid": "15",
  "total": "123",
  "updated": "yes"
},
   {
  "projectid": "24",
  "total": "123",
  "updated": "yes"
}]}

Am using datawevae to merge these payload but its not giving expected result my expected value is

 {
 "Result": [
{
  "projectid": "15",
  "total": "123",
  "approval_status": "A"
},
   {
  "projectid": "24",
  "total": "123",
  "approval_status": "A"
}]}

Answer 1

I am not sure how are you getting both the payloads at the same time

But if you are able to get both the payload and able to save into 2 flow variables, then you can use an Expression transformer to construct your combined payload.
Below is a sample you can try to combine both the payload dynamically:-

     <set-payload value="{
    &quot;first&quot;: [{
        &quot;projectid&quot;: &quot;15&quot;,
        &quot;approval_status&quot;: &quot;A&quot;
    }, {
        &quot;projectid&quot;: &quot;24&quot;,
        &quot;approval_status&quot;: &quot;A&quot;
    }]
   }" mimeType="application/json" doc:name="Set Payload"/>
       <json:json-to-object-transformer returnClass="java.util.HashMap" doc:name="JSON to Object"/>
        <set-variable variableName="var1" value="#[message.payload]" doc:name="Variable"/>

        <set-payload value="{
    &quot;Second&quot;: [{
        &quot;projectid&quot;: &quot;15&quot;,
        &quot;total&quot;: &quot;123&quot;,
        &quot;updated&quot;: &quot;yes&quot;
    }, {
        &quot;projectid&quot;: &quot;24&quot;,
        &quot;total&quot;: &quot;123&quot;,
        &quot;updated&quot;: &quot;yes&quot;
    }]
  }" mimeType="application/json" doc:name="Set Payload"/>
        <json:json-to-object-transformer returnClass="java.util.HashMap" doc:name="JSON to Object"/>
        <set-variable variableName="var2" value="#[message.payload]" doc:name="Variable"/>

        <expression-transformer
     expression="#[[

     'Result':[
     {
      'projectid': flowVars.var1.first[0].projectid,
      'total': flowVars.var2.Second[0].total,
      'approval_status': flowVars.var1.first[0].approval_status
    },
    {
     'projectid': flowVars.var1.first[1].projectid,
     'total': flowVars.var2.Second[1].total,
     'approval_status': flowVars.var1.first[1].approval_status
   }

  ]]
      ]" doc:name="Expression"/>
  <json:object-to-json-transformer doc:name="Object to JSON"/>     

Please note, the above example is just to show how you can combine the payload dynamically and quickly.
Here you need to store both the payload in a flow variable var1 and var2, (I have used set payload just to demonstrate, you need to get it dynamically) , then you can construct the payload dynamically using the expression transformer
Also note, if you are getting the json payload in the exact same structure, you can use the above code for a quick combination of payloads...
But, if your json structure changes say one more list element included in your payload, then you need to modify the above code and use a for loop to get the values.

The above code will produce a quick combined payload as follows:-

{
  "Result": [
    {
      "total": "123",
      "projectid": "15",
      "approval_status": "A"
    },
    {
      "total": "123",
      "projectid": "24",
      "approval_status": "A"
    }
  ]
}

Answer 2

Edited Answer Now it will filter dynamically based on ur ID.

                        %dw 1.0
%output application/json
%var json1 = {"first": [{  "projectid": "15", "approval_status": "A"},{  "projectid": "24",  "approval_status": "A"}  ]}
%var json2 = { "Second": [{  "projectid": "15",  "total": "123",  "updated": "yes"},   {  "projectid": "24",  "total": "123",  "updated": "yes"}]}
---

Result: ( using(json2Group = json2.Second groupBy $.projectid) (json1.first  filter ( json2Group[$.projectid] != null)  map (( $  ++ json2Group[$.projectid][0]) ) distinctBy $  )    )