Remove duplicate values in JSON file

[spuri5]

Hello, 

In my input file, I have Areacode, Areaname, StreetCode, and Zipcode, repeating multiple times and i just need it to show only once like in my expected outcome.

Also i want location to have nested values under location as shown below in the expected output.

Sample Input:

Expected Output:

[Luke_C]

Hi @spuri5 

Please include your data instead of screenshots. You'd first want to look at using a unique tool to get rid of the duplicates. Otherwise the JSON Build tool should do the trick.

[spuri5]

Please find the sample data below:

Record ID	Name	Value
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2027-04-05
1	Location.lpCode	AP0BUY
1	Location.lpDescription	AA
1	Location.lpYear	2090
1	zipCode	FFAQ,FI19IA
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2029-04-06
1	Location.lpCode	AP0ABC
1	Location.lpDescription	ZZ
1	Location.lpYear	2078
1	zipCode	FFAQ,FI19IA
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2033-04-06
1	Location.lpCode	AP1LKJ
1	Location.lpDescription	QQ
1	Location.lpYear	2087
1	zipCode	FFAQ,FI19IA
2	AreaCode	CD123
2	AreaName	CD123_name
2	StreetCode	ABT
2	Location.Date	4/6/2032
2	Location.lpCode	AP1GTB
2	Location.lpDescription	BH
2	Location.lpYear	2094
2	zipCode	FFAQ,FI19IA,SGHTWY
2	AreaCode	CD123
2	AreaName	CD123_name
2	StreetCode	ABT
2	Location.Date	4/6/2030
2	Location.lpCode	AP1QWR
2	Location.lpDescription	OI
2	Location.lpYear	2077
2	zipCode	FFAQ,FI19IA,SGHTWY

[Luke_C]

Hi @spuri5 

Here's how you can do this:

1. Record ID to keep order
2. Unique tool to remove dupes
3. Sort to put back in order (based on record ID)
4. JSON Build
5. Summarize to create the list

[spuri5]

Hi Luke,

Below is the output I am getting with your workflow:

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

But the expected out should be as shown below: Where the location should be appearing once!

[
{
"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"
}
]
},
{
"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"
}
]
}
]

[Luke_C]

Hi @spuri5 

I see, let me look.

[Luke_C]

Hi @spuri5 

Give this a go. Had to handle the multiple locations per record. 

For future reference, the best way to figure out how to structure your data is to have your expected JSON as an input and see what the JSON Parse tool gives you. Then you just need to get alteryx to put the data in that format ahead of a JSON Build to get the same results

[spuri5]

Hi Luke,

Thank you so much for your help, it was my bad I forgot to add the values of the attributes (Location.Date and Location.lpCode)  within the "location" container that can be repeated for a larger set of data. Hence uniquing them out based on record id, name, and value is eliminating the duplicates which shouldn't be the case.

We want to retain all values as shown below.

Input File: Please use this file as an Input

Record ID	Name	Value
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2027-04-05
1	Location.lpCode	AP0BUY
1	Location.lpDescription	AA
1	Location.lpYear	2090
1	zipCode	FFAQ,FI19IA
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2027-04-05
1	Location.lpCode	AP0BUY
1	Location.lpDescription	ZZ
1	Location.lpYear	2078
1	zipCode	FFAQ,FI19IA
1	AreaCode	AB123
1	AreaName	AB123_name
1	StreetCode	TRC
1	Location.Date	2033-04-06
1	Location.lpCode	AP1LKJ
1	Location.lpDescription	QQ
1	Location.lpYear	2087
1	zipCode	FFAQ,FI19IA
2	AreaCode	CD123
2	AreaName	CD123_name
2	StreetCode	ABT
2	Location.Date	4/6/2032
2	Location.lpCode	AP1GTB
2	Location.lpDescription	BH
2	Location.lpYear	2094
2	zipCode	FFAQ,FI19IA,SGHTWY
2	AreaCode	CD123
2	AreaName	CD123_name
2	StreetCode	ABT
2	Location.Date	2030-04-06
2	Location.lpCode	AP1QWR
2	Location.lpDescription	OI
2	Location.lpYear	2094
2	zipCode	FFAQ,FI19IA,SGHTWY

[Luke_C]

Hi @spuri5 

Just a matter of only checking non-location fields for duplicates.

[jakaur32]

thank you so much Luke, this workflow was really helpful for me as well.