Transforming denormalized data to a single message

I have CSV similar to:

Id,FName,Lname,street,city,state
15,Mary,Smith,123 Main,Anytown,NY
15,Mary,Smith,456 Elm,Sometown,PA
22,Joe,Know,123 4th,BigTown,NJ
22,Joe,Know,456 Shade,NoTown,VA

There can be any number of the id’s which means that any person can have any number of addresses. What I want to do is transform this to group on the id’s so the addresses are an array of objects under the name. This seems very similar to Transformation for data or Map data to json structure but just different enough where I am not able to figure it out.

Target JSON would be:

[
{
“attributes”: {
“FirstName”: [
{
“value”: “Mary”
}
],
“LastName”: [
{
“value”: “Smith”
}
],
“Address”: [
{
“value”: {
“AddressLine1”: [
{
“value”: “123 Main St”
}
],
“City”: [
{
“value”: “Anytown”
}
],
“StateProvince”: [
{
“value”: “NY”
}
]
}
},
{
“value”: {
“AddressLine1”: [
{
“value”: “456 Elm St”
}
],
“City”: [
{
“value”: “Sometown”
}
],
“StateProvince”: [
{
“value”: “PA”
}
]
}
}
]
}
},
{
“attributes”: {
“FirstName”: [
{
“value”: “Joe”
}
],
“LastName”: [
{
“value”: “Know”
}
],
“Address”: [
{
“value”: {
“AddressLine1”: [
{
“value”: “123 4th”
}
],
“City”: [
{
“value”: “Bigtown”
}
],
“StateProvince”: [
{
“value”: “NJ”
}
]
}
},
{
“value”: {
“AddressLine1”: [
{
“value”: “456 Shade”
}
],
“City”: [
{
“value”: “NoTown”
}
],
“StateProvince”: [
{
“value”: “VA”
}
]
}
}
]
}
}
]

Attached is an option using the Group By Fields snap. It can likely be improved.

community-1806_2017_12_14.slp (6.3 KB)

1 Like

Here is another method, more Snaps, just a different approach.


PivotAddresses_2017_12_14.slp (8.2 KB)

1 Like

Wow! You are quite the mapping expert!

i saw the jsonPath.map method, but couldn’t really figure it out. would you indulge me with an explanation of the x=> part of the expression?

No. I’m a better doer than explainer. Besides, you’re …TooOldToLearn anyway. :slight_smile:

An expert can follow up to correct me, but I’ll do my best to explain… The “x=>” is known in JavaScript as an arrow function. It’s kind of shorthand for an anonymous function. It’s similar to what’s known as a lambda expression in Java and C#.

In this case, it’s used as a callback function to the Array.map() method. The map() method creates a new array with the results of the callback function as it iterates through each element in the array. So “x” in “x=>” represents the individual array element. The code after the “=>” is the logic to be performed with “x” or other globally scoped variables. The results of the function are appended to the output array.

I hope that helps.

1 Like