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 - #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”
}
]
}
}
]
}
}
]
Attached is an option using the Group By Fields snap. It can likely be improved.
community-1806_2017_12_14.slp (6.3 KB)
