Skip to main content

I'm working with a JSON file that is a form configuration. Users complete the form through a company website and there are lots of conditional questions and picklists embedded within questions. It's a real nesting doll of data.

 

Our form system has gotten bloated so I am taking redundant forms and creating one normalized form, but first I need to break down the configuration of each form and represent it in a CSV format so I can easily see the flow of questions and their sub questions. So far I have been able to download the JSON file, add a reader and perform a couple transformations (reader > list_populator > attribute_keeper > list_exploder > list_builder) to isolate the overarching questions and organize each by an index number (the order which they appear in the form). In total there are 8 main branches of questions, but each of those has dozens of nested sub-layers that go deeper and deeper; many of which are long picklists.

 

From here, I want to be able to isolate each main-branch question and then basically flatten all the layers within that question branch to be able to write to CSV, but everything I try to do feels like a dead end.

Hello @dcox​ 

I would recommend going through this article on JSON transformations. The JSON reader is ideal for JSON data has a fixed schema but it sounds like you have a variable number of nested levels in your data. In this case, the JSON transformers may be more helpful.

It may also help if you can provide a sample of the input data and also a sample of how you want the data represented in the output CSV.

Sure, I don't mind sharing the form config as it contains no sensitive information.

Below is the workflow that I used to (almost) flatten the nested levels into a the schema that I wanted. You can see in the feature viewer on the right hand the layers of nested data within each field.

 

imageHere is a rough csv schema for what I was wanting the csv output to look like:

 

image.pngIt would be great if I could sort everything in descending layers starting with the central field (which in this case is the key value for the question branch and all it's paths) and then moving to the main question and then moving through every conditional field after that.

Sure, I don't mind sharing the form config as it contains no sensitive information.

Below is the workflow that I used to (almost) flatten the nested levels into a the schema that I wanted. You can see in the feature viewer on the right hand the layers of nested data within each field.

 

imageHere is a rough csv schema for what I was wanting the csv output to look like:

 

image.pngIt would be great if I could sort everything in descending layers starting with the central field (which in this case is the key value for the question branch and all it's paths) and then moving to the main question and then moving through every conditional field after that.

Hello @dcox​ 

The attached workspace doesn't quite create the output you want, but you should be able modify it to create the output you want. In this workspace, JSONFragmenters are used to fragment each question while a ListConcatentor is used to concatenate options for the question if it exists. I hope this helps!

Hello @dcox​ 

The attached workspace doesn't quite create the output you want, but you should be able modify it to create the output you want. In this workspace, JSONFragmenters are used to fragment each question while a ListConcatentor is used to concatenate options for the question if it exists. I hope this helps!

This is massively helpful. Thank you. The JSONFragmenters were definitely the missing piece to this puzzle. Your solution is so simple, I definitely need to invest more time into learning FME, it is so powerful in capable hands.

Community Stats

32,055
Posts
121,712
Replies
39,681
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.