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

JSON to infotable or Merge rows based on PrimaryKey

SOLVED
mvajla
Marble

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

Re: JSON to infotable or Merge rows based on PrimaryKey

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

Re: JSON to infotable or Merge rows based on PrimaryKey

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.

 

Re: JSON to infotable or Merge rows based on PrimaryKey

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

Re: JSON to infotable or Merge rows based on PrimaryKey

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

Re: JSON to infotable or Merge rows based on PrimaryKey

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

Re: JSON to infotable or Merge rows based on PrimaryKey

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

View solution in original post

Announcements

Check out the upcoming Expert Session: Understanding ThingWorx Navigate Licensing in Community "Customer Events" section.