Merge elements in JSON within Mule 4

Question

I have the following SQL Server select query:

SELECT s.RefId
          ,s.LocalId
          ,s.StateProvinceId
          ,s.SchoolName
          ,e.Email
          ,e.EmailType
      FROM SchoolInfo s
      LEFT OUTER JOIN SchoolEmail e
      ON    e.SchoolRefId = s.RefId
      WHERE s.RefId = :ref_id

Converted to JSON in DataWeave:

%dw 2.0
output application/json
---
payload

Output:

[
  {
    "StateProvinceId": "SA",
    "RefId": "7FDF722B-6BBA-4BF0-8205-A5380B269EF1",
    "EmailType": "prm",
    "LocalId": "1",
    "SchoolName": "Steve's School",
    "Email": "[email protected]"
  },
  {
    "StateProvinceId": "SA",
    "RefId": "7FDF722B-6BBA-4BF0-8205-A5380B269EF1",
    "EmailType": "sec",
    "LocalId": "1",
    "SchoolName": "Steve's School",
    "Email": "[email protected]"
  }
]

But I wish to have it merged by the common elements to generate the desired output of:

{
  "RefId": "7FDF722B-6BBA-4BF0-8205-A5380B269EF1",
  "LocalId": "1",
  "StateProvinceId": "SA",
  "SchoolName": "Steve's School",
  "Emails": [
    {
      "Email": "[email protected]",
      "EmailType": "prm"
    },
    {
      "Email": "[email protected]",
      "EmailType": "sec"
    }
  ]
}

How can I do that in Mule 4?

Thanks, Steve

Answer 1

Or even this

%dw 2.0
output application/json
var Emails =  
    "Emails": payload map {
      Email: $.Email,
      EmailType: $.EmailType
}

---
payload distinctBy($.StateProvinceId) map {
  StateProvinceId: $.StateProvinceId,
  RefId: $.RefId,
  Emails:  Emails.Emails
} 

Answer 2

(payload  map {
  StateProvinceId: $.StateProvinceId,
  RefId: $.RefId,
  Emails:  Emails.Emails
})[0]

Answer 3

I hope this gives you the idea.

%dw 2.0
output application/json
var Emails =  [
    "Emails": payload map {
      Email: $.Email,
      EmailType: $.EmailType
}
]
---
payload distinctBy($.StateProvinceId) map {
  StateProvinceId: $.StateProvinceId,
  RefId: $.RefId,
  Emails:  Emails.Emails[0]
}   

Answer 4

This probably can give you a more generic approach:

%dw 2.0
output application/json
fun returnEmail() =  
      payload groupBy($.StateProvinceId) mapObject {
         ($$): $  map {
               Email: $.Email,
               EmailType: $.EmailType
         }
} 


---
payload distinctBy($.StateProvinceId) map {
  StateProvinceId: $.StateProvinceId,
  RefId: $.RefId,
  Emails: returnEmail()[$.StateProvinceId]
}

Answer 5

I modified the payload a bit to have another ProvinceId in it.

Answer 6

@Salim Khan answer is perfectly right. Just adding up reduce to convert array to object as a last bit to match exactly with your response format

%dw 2.0
var email =   payload map (item,index) -> { 
"Email":item.Email,
"EmailType":item.EmailType }

output application/json
---
 payload distinctBy($.StateProvinceId) map  {
"StateProvinceId": $.StateProvinceId,
"RefId": $.RefId,
 "LocalId":  $.LocalId,
"SchoolName": $.SchoolName,
"Emails":email } reduce $