Nest Multiple container

[jakaur32]

Hello,

I have a set of data that needs to be broken  into separate sub groups. attaching is the workflow I have so far, however my expect outcome is not matching. The outcome that I am getting with this workflow is not correct so I am also pasting what my outcome should look like in json format.  Please Advise, thank you.

[
{
"AreaCode":"AB123",
"AreaName":"AB123_name",
"StreetCode":"TRC",
"zipCode":"FFAQ,FI19IA",
"Location":[
{
"Date":"2027-04-05",
"lpCode":"AP0BUY",
"lpDescription":"AA",
"lpYear":"2090"
},
{
"Date":"2029-04-06",
"lpCode":"AP0ABC",
"lpDescription":"ZZ",
"lpYear":"2078"
},
{
"Date":"2033-04-06",
"lpCode":"AP1LKJ",
"lpDescription":"QQ",
"lpYear":"2087"
}
]
"Fruit":[
{
"Fruit.Apple":"ABC",
"Fruit.No":"12",
},
{
"Fruit.Apple":"DEF",
"Fruit.No":"12",
}
]
},
{
"AreaCode":"CD123",
"AreaName":"CD123_name",
"StreetCode":"ABT",
"zipCode":"FFAQ,FI19IA,SGHTWY",
"Location":[
{
"Date":"2032-04-06",
"lpCode":"AP1GTB",
"lpDescription":"BH",
"lpYear":"2094"
},
{
"Date":"2030-04-06",
"lpCode":"AP1QWR",
"lpDescription":"OI",
"lpYear":"2077"
}
]
"Fruit":[
{
"Fruit.Apple":"ABC",
"Fruit.No":"12",
},
{
"Fruit.Apple":"DEF",
"Fruit.No":"23",
}
]
}
]

[IraWatt · Accepted solution]

Hey @jakaur32,

I think I've got the JSON output which you want. Can you confirm that its as you wanted it?

I'm pretty sure it can be simplified and if it works I can help explain it and adjustments you could make to yours to get it to work.

HTH,

Ira

[jakaur32]

Hi Ira,

Thank you for getting back to me. The expected outcome is still not how it should be. I am copying what my expected outcome should be and what I am currently getting, please take a look.  

#The schema looks perfectly fine as expected, but just needs few adjustments with the data inside
#I fixed the repeating attributes (AreaCode, AreaName,StreetCode and zipCode) that I just want to see once
#I have updated the input data by completely removing the Mango attribute as I just want 'Apple' & 'No'
#My next requirement is to have the 'Fruit' container values to be a group of unique 'Apple' & 'No' values

#EXPECTED Output
[
{
"AreaCode": "AB123",
"AreaName": "AB123_name",
"StreetCode": "TRC",
"zipCode": "FFAQ,FI19IA",
"Location": [
{
"Date": "2027-04-05",
"lpCode": "AP0BUY",
"lpDescription": "AA",
"lpYear": "2090"
},
{
"Date": "2027-04-05",
"lpCode": "AP0BUY",
"lpDescription": "ZZ",
"lpYear": "2078"
},
{
"Date": "2033-04-06",
"lpCode": "AP1LKJ",
"lpDescription": "QQ",
"lpYear": "2087"
}
],
"Fruit": [
{
"Apple": "ABC",
"No": "12"
},
{
"Apple": "DEF",
"No": "12"
}
]
},
{
"AreaCode": "CD123",
"AreaName": "CD123_name",
"StreetCode": "ABT",
"zipCode": "FFAQ,FI19IA,SGHTWY",
"Location": [
{
"Date": "2032-04-06",
"lpCode": "AP1GTB",
"lpDescription": "BH",
"lpYear": "2094"
},
{
"Date": "2030-04-06",
"lpCode": "AP1QWR",
"lpDescription": "OI",
"lpYear": "2094"
}
],
"Fruit": [
{
"Apple": "ABC",
"No": "12"
},
{
"Apple": "DEF",
"No": "23"
}
]
}
]

#CURRENT Outcome

[
{
"AreaCode": "AB123",
"AreaName": "AB123_name",
"StreetCode": "TRC",
"zipCode": "FFAQ,FI19IA",
"Location": [
{
"Date": "2027-04-05",
"lpCode": "AP0BUY",
"lpDescription": "AA",
"lpYear": "2090"
},
{
"Date": "2027-04-05",
"lpCode": "AP0BUY",
"lpDescription": "ZZ",
"lpYear": "2078"
},
{
"Date": "2033-04-06",
"lpCode": "AP1LKJ",
"lpDescription": "QQ",
"lpYear": "2087"
}
],
"Fruit": [
{
"Apple": "ABC",
"No": "12"
},
{
"Apple": "DEF",
"No": "12"
},
{
"Apple": "ABC",
"No": "12"
},
{
"Apple": "ABC",
"No": "12"
}
]
},
{
"AreaCode": "CD123",
"AreaName": "CD123_name",
"StreetCode": "ABT",
"zipCode": "FFAQ,FI19IA,SGHTWY",
"Location": [
{
"Date": "2032-04-06",
"lpCode": "AP1GTB",
"lpDescription": "BH",
"lpYear": "2094"
},
{
"Date": "2030-04-06",
"lpCode": "AP1QWR",
"lpDescription": "OI",
"lpYear": "2094"
}
],
"Fruit": [
{
"Apple": "ABC",
"No": "12"
},
{
"Apple": "DEF",
"No": "23"
},
{
"Apple": "ABC",
"No": "12"
}
]
}
]

[IraWatt]

@jakaur32 No worries, I'll have a look at sorting the new expected outcome