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": "steven@email.com"
},
{
"StateProvinceId": "SA",
"RefId": "7FDF722B-6BBA-4BF0-8205-A5380B269EF1",
"EmailType": "sec",
"LocalId": "1",
"SchoolName": "Steve's School",
"Email": "test@gmail.com"
}
]
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": "steven@email.com",
"EmailType": "prm"
},
{
"Email": "test@gmail.com",
"EmailType": "sec"
}
]
}
How can I do that in Mule 4?
Thanks, Steve
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
}
Thanks Salim! I was trying to do everything after the ---, I didnt even think of extracting the Emails like this! Questions: Why did you do the distinctBy StateProvinceID? This looks like it generates an array: [ { "RefId": "7FDF722B-6BBA-4BF0-8205-A5380B269EF1", "LocalId": "1", "SchoolName": "Steve's School", "StateProvinceId": "SA", "Emails": [ { "Email": "steven@gmail.com", "EmailType": "prm" }, { "Email": "test@gmail.com", "EmailType": "sec" } ] } ] How can we remove the top array?
distinctBy so that you don't repeat the keys that you need only once in the input. As well you could do something like this to get the output
Do you happen to have some other inputs (different stateProvinceId) that would help make it a generic solution.
RefId is the unique key, so I think distinctBy on that element should be fine. "As well you could do something like this to get the output " - did you finish your message?
yup .. posted below by accessing the [0]the element of the final array