cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Transforming denormalized data to a single message

NeverTooOldToLe
New Contributor III

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 - #3 by eguo 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โ€
}
]
}
}
]
}
}
]

1 ACCEPTED SOLUTION

del
Contributor III

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

community-1806_2017_12_14.slp (6.3 KB)

View solution in original post

4 REPLIES 4

del
Contributor III

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

community-1806_2017_12_14.slp (6.3 KB)

NeverTooOldToLe
New Contributor III

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. ๐Ÿ™‚

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.

cstewart
Former Employee

Here is another method, more Snaps, just a different approach.
image
PivotAddresses_2017_12_14.slp (8.2 KB)