Skip to main content
  • EN

Hi,

 

I have a JSON that I want to "flatten”, Currently, it has this structure. 

{"A":{"id":"1","Name":"Aa","IsTrue":"True"},"1stList":s{"Type":"Yes","Number":"100"},{"Type":"No","Number":"100"},{"Type":"Maybe","Number":"1000"},{"Type":"Unknown","Number":"11"},{"Type":"Hh"}],"2ndList":s{"X":"10","Y":"100","Z":"Abc","Q":"Def"}]}
{"A":{"id":"2","Name":"Bb","IsTrue":"True"},"2ndList":s{"X":"10","Y":"11","R":"100000","Z":"Efg","Q":"Klmo"}]}
{"A":{"id":"3","Name":"Cc","IsTrue":"True"},"2ndList":s{"X":"10","Y":"12","Z":"Lmno","Q":"Stuv"}]}

Just reading it with the following parameter settings doesn't work:

Flatten Nested JSON Values into Attributes: YES

 

I've tried with 2 listexploders after reading, but that also doesn't give the correct result.

I just want the endresult to be a flat table. How can I best achieve this in FME?

 

@redgeographics  , @takashi  are you able to help?

How exactly are you wanting this “flattened”? Wondering what attributes you are expecting, are you wanting the “Types” from the first list to be an attribute?

If you are not flattening Nested JSON Values into Attributes, then you could use the JSONExtractor to create the attributes you want.

Flattening the Nested JSON in the reader seems to work for me though. If you are wanting the 1stList “Types” to be attributes, you could try the AttributePivoter followed by the AttributeExposer. This worked for me, but I am not exactly sure what you are looking for as an end result.

How exactly are you wanting this “flattened”? Wondering what attributes you are expecting, are you wanting the “Types” from the first list to be an attribute?

If you are not flattening Nested JSON Values into Attributes, then you could use the JSONExtractor to create the attributes you want.

Flattening the Nested JSON in the reader seems to work for me though. If you are wanting the 1stList “Types” to be attributes, you could try the AttributePivoter followed by the AttributeExposer. This worked for me, but I am not exactly sure what you are looking for as an end result.

This is what I mean, see image. When I read the json, I can see all the attributes (1). However, when I want to write the results in another output file, or transform the data further, the "list attributes” are not available (2).

Exploding the lists resulted in error output for one of the lists, so that didn't work quite well.

I just want the list attributes to be available (exposed), and then as flat as possible. Either by "unlisting” them, or maybe by putting the values of the lists as a concatinated atrribute value.

 

Hi @fmenco ,

Do you need to create these two tables from the JSON document?

 

Hi @fmenco ,

Do you need to create these two tables from the JSON document?

 

No, I need to create just one table with all of the output flattened as much as possible. In (1), the reader, you can see that all the output is present. I just don't get them down the processing line.

@fmenco For the ListExploder, it cannot explode a list that does not exist on a feature and that causes it to fail. So for the second and third features, since they do not have a 1st list they will cause a failure. You can resolve this by either setting your workspace parameters to not terminate translation on a failure, or use a Tester before the ListExploder to ensure that only features that have the list you want to explode enter the ListExploder. I will get you a screenshot of how this looked for me.

There are a lot of ways to solve this, here are two that sound similar to how you may have started to solve it. I turned off Terminate Translation on Rejected Feature

Reply


Community Stats

30,351
Posts
115,341
Replies
38,080
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.