Community Tip - Learn all about PTC Community Badges. Engage with PTC and see how many you can earn! X
Hi everbody,
do somebody have an experience with case when you need to convert JSON to Infotable, but when in JSON are two or more objects with the same primary key and you need this into one row of infotable?
Example:
from JSON:
MaterialNumber | Color |
123456789 | Red, Blue |
MaterialNumber | Color |
123456789 | Red |
123456789 | Blue |
and you would like to have as this:
MaterialNumber | Color |
123456789 | Red, Blue |
Any ideas?
Solved! Go to Solution.
We had to update a structure of JSON, then it was quite easy to parse common attributes.
I ran this example through two different json format validators, and it looks like the entries should be comma separated to be validated (no brace between them):
{
"MaterialNumber": "123456789",
"Color": "Red"
,
"MaterialNumber": "123456789",
"Color": "Blue"
}
One validator (JSONLint) cited the duplicate key (MaterialNumber) as a syntax error, while the other (JSON formatter) accepted this form as valid json. In either case, this is a comma separated list of entries, and OOTB Navigate will display each entry on a separate line by default. So, you get your example of the table with 2 lines. To get both colors on the same line, you would need the json to be:
{
"MaterialNumber": "123456789",
"Color": "Red, Blue"
}
Both validators recognize this as valid json. To get this, you would need to write some custom code to convert the json returned by the query to combine the color values for a specific MaterialNumber into this format. Then provide this customized json to the infotable, and it should display as you desire.
Hi @mvajla.
If the previous response answered your question, please mark it as the Accepted Solution for the benefit of others on the community.
Regards.
--Sharon
Hi @mvajla.
If the previous response answered your question, please mark it as the Accepted Solution for the benefit of others on the community.
Regards.
--Sharon
A simple JS CB can meet your requirement.
Ex.
const inp1 = [
{
"MaterialNumber": "123456789",
"Color": "Red"
},
{
"MaterialNumber": "123456789",
"Color": "Blue"
}];
const consolidateBy = (inp1) => (
Object.assign(
inp1[0], {
Color: inp1.map(set => set.Color).join(',')
}
)
);
console.log(consolidateBy(inp1));
===
{ MaterialNumber: '123456789', Color: 'Red,Blue' }
We had to update a structure of JSON, then it was quite easy to parse common attributes.