Solved

How to flatten nested JSON?

  • 28 October 2019
  • 5 replies
  • 157 views
cheffing
Badge

Hi,

I tried several times to get a flat table of this JSON-File: .

 

It comes out of an API-Request and has this structure:

Target is a flat table with this columns to be able to join it with the corresponding shapefile:

  • stunde
  • bstid
  • height
  • timeslice
  • WIND_6H
  • RISK_WINDBREAKAGE_BEECH_MAX_6H
  • RISK_WINDBREAKAGE_SPRUCE_MAX_6H
  • PROB_GUST_100kmh_MAX_6H
  • PROB_GUST_75kmh_MAX_6H
  • PROB_GUST_60kmh_MAX_6H

Thought about something like this within the JSON-Extractor:

But can't get it to work like I expected.

Looking forward to your help.

Best regards, Christoph

icon

Best answer by daveatsafe 28 October 2019, 19:45

View original

5 replies

H
Badge +2

Hi @cheffing,

I think you have two options here:

a) Save this JSON response from the API request to a file and then use a FeatureReader to read it in - this will flatten the JSON for you and return all these attributes without a need for you to build any JSON Queries.

b) If the JSON response is coming in from an attribute use a JSONFragmenter, leave the JSON Query as json[*] and then under Flattening Parameters set Flatten Query Result into Attributes = Yes and under Attributes to Expose add the names of all your attributes. A number of your attributes:

  • timeslice
  • WIND_6H
  • RISK_WINDBREAKAGE_BEECH_MAX_6H
  • RISK_WINDBREAKAGE_SPRUCE_MAX_6H
  • PROB_GUST_100kmh_MAX_6H
  • PROB_GUST_75kmh_MAX_6H
  • PROB_GUST_60kmh_MAX_6H

are actually lists so when you add these you will need to specify {} for these to be exposed. E.g. timeslice{} and then you can explode these into actual attributes.

 

jdh
Rank
Badge +22

I expect you want a JSONFragmenter with json[*] so you end up with 4 features.. You can set the Flattening Parameters to Yes. Whether you want to recursive or not depends on whether you want your timeslices to be a list, or stay as a JSON array.

 

 

daveatsafe
Rank
Userlevel 2
Badge +17

Hi @cheffing,

You can use the JSON reader, (or a JSONFlattener set to read from file) to read the JSON records from the file and flatten the attribute structure.

However, the resulting attributes will mostly be lists, which cannot be written to Shape files, so you will need to explode these lists into individual features. The simplest way to do this is to explode the timeslices list with a ListExploder, then use ListIndexers to get the matching data from the other lists, using the index attribute produced by the ListExploder.

I am attaching a workspace to illustrate the process.

json2shape.fmw

cheffing
Badge

Hi @cheffing,

You can use the JSON reader, (or a JSONFlattener set to read from file) to read the JSON records from the file and flatten the attribute structure.

However, the resulting attributes will mostly be lists, which cannot be written to Shape files, so you will need to explode these lists into individual features. The simplest way to do this is to explode the timeslices list with a ListExploder, then use ListIndexers to get the matching data from the other lists, using the index attribute produced by the ListExploder.

I am attaching a workspace to illustrate the process.

json2shape.fmw

Hi Dave, your project helped! Thanks a lot! My problem was to get the list-Attributes in the json-query and your example works fine.

 

Best regards, Christoph
cheffing
Badge

Hi @cheffing,

I think you have two options here:

a) Save this JSON response from the API request to a file and then use a FeatureReader to read it in - this will flatten the JSON for you and return all these attributes without a need for you to build any JSON Queries.

b) If the JSON response is coming in from an attribute use a JSONFragmenter, leave the JSON Query as json[*] and then under Flattening Parameters set Flatten Query Result into Attributes = Yes and under Attributes to Expose add the names of all your attributes. A number of your attributes:

  • timeslice
  • WIND_6H
  • RISK_WINDBREAKAGE_BEECH_MAX_6H
  • RISK_WINDBREAKAGE_SPRUCE_MAX_6H
  • PROB_GUST_100kmh_MAX_6H
  • PROB_GUST_75kmh_MAX_6H
  • PROB_GUST_60kmh_MAX_6H

are actually lists so when you add these you will need to specify {} for these to be exposed. E.g. timeslice{} and then you can explode these into actual attributes.

 

Hi Holly, the {} within the query was the solution. Thanks for your hints! Best regards, Christoph

Reply


Community Stats

28,707
Posts
109,479
Replies
36,552
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.