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

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

JSON to infotable or Merge rows based on PrimaryKey

mvajla
12-Amethyst

JSON to infotable or Merge rows based on PrimaryKey

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: 

 "results": [
            {
                "MaterialNumber""123456789",
                "Color": "Red"
            },
         
                "MaterialNumber""123456789",
                "Color": "Blue"
            }
        ]
 
to infotable:
MaterialNumber Color
123456789 Red, Blue
 
 
OR case if you have an infotable as this: 
MaterialNumber Color
123456789 Red
123456789 Blue

 

and you would like to have as this: 

MaterialNumber Color
123456789 Red, Blue

 

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
mvajla
12-Amethyst
(To:mvajla)

We had to update a structure of JSON, then it was quite easy to parse common attributes.

View solution in original post

5 REPLIES 5
barko
16-Pearl
(To:mvajla)

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.

 

slangley
23-Emerald II
(To:mvajla)

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

slangley
23-Emerald II
(To:slangley)

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

Swamy_Senthil
6-Contributor
(To:mvajla)

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' }

mvajla
12-Amethyst
(To:mvajla)

We had to update a structure of JSON, then it was quite easy to parse common attributes.

Top Tags