Skip to main content
Question

Flattening JSONS that contain lists

fmenco
Contributor
Forum|alt.badge.img+5

Hi,

 

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

{"A":{"id":"1","Name":"Aa","IsTrue":"True"},"1stList":[{"Type":"Yes","Number":"100"},{"Type":"No","Number":"100"},{"Type":"Maybe","Number":"1000"},{"Type":"Unknown","Number":"11"},{"Type":"Hh"}],"2ndList":[{"X":"10","Y":"100","Z":"Abc","Q":"Def"}]}
{"A":{"id":"2","Name":"Bb","IsTrue":"True"},"2ndList":[{"X":"10","Y":"11","R":"100000","Z":"Efg","Q":"Klmo"}]}
{"A":{"id":"3","Name":"Cc","IsTrue":"True"},"2ndList":[{"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?

 

7 replies

fmenco
Contributor
Forum|alt.badge.img+5
  • fmencoContributor
  • Author
  • Contributor
  • August 22, 2024

@redgeographics  , @takashi  are you able to help?

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
liamfez
Influencer
Forum|alt.badge.img+34
  • liamfezInfluencer
  • Influencer
  • August 22, 2024

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.

not a bot
danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
fmenco
Contributor
Forum|alt.badge.img+5
  • fmencoContributor
  • Author
  • Contributor
  • August 23, 2024
liamfez wrote:

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.

 

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
takashi.pss
Contributor
Forum|alt.badge.img+3
  • takashi.pssContributor
  • Contributor
  • August 23, 2024

Hi @fmenco ,

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

 

danilo_fme
1 person upvoted this
  • danilo_fme
    danilo_fme
fmenco
Contributor
Forum|alt.badge.img+5
  • fmencoContributor
  • Author
  • Contributor
  • August 23, 2024
takashi.pss wrote:

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.

liamfez
Influencer
Forum|alt.badge.img+34
  • liamfezInfluencer
  • Influencer
  • August 23, 2024

@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.

not a bot
liamfez
Influencer
Forum|alt.badge.img+34
  • liamfezInfluencer
  • Influencer
  • August 23, 2024

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

not a bot

Reply


Most helpful members this week

Community Stats

30,846
Posts
116,939
Replies
38,670
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.