Skip to main content
Solved

Creating tabular data from JSON response

  • December 19, 2019
  • 5 replies
  • 122 views
mattebridges
Participant
Forum|alt.badge.img+5

Hello - sorry if this is a very basic question but I'm struggling to convert a JSON response that is received from an API in to tabular data.

The response is sourced as per the following:

I've included two objects (apologies if 'object' is not the correct term) in the following .txt:

I would like to generate tabular data from this response - i.e. based on the attached file I would like a new row for each 'object' (denoted by the "OrganisationID" value) with a selection of the values within this 'object' as attributes on the row.

I've read the JSON tutorials and have tried various combinations of JSONFlattener, JSONExtractor and JSONFragmenter but with no joy.

I would be extremely grateful for any advice that anyone can offer.

 

Thanks in advance,

 

Matt

 

Best answer by jdh

Assuming the response is proper json (the attached file is not), a JSONFragmenter set to json["value"][*] with flattening enabled should give you what you want.

 

View original
1 Attachments
Did this help you find an answer to your question?

5 replies

jdh
Contributor
Forum|alt.badge.img+28
  • jdhContributor
  • Contributor
  • Best Answer
  • December 19, 2019

Assuming the response is proper json (the attached file is not), a JSONFragmenter set to json["value"][*] with flattening enabled should give you what you want.

 

mattebridges
Participant
Forum|alt.badge.img+5
  • mattebridgesParticipant
  • Author
  • Participant
  • December 19, 2019
jdh wrote:

Assuming the response is proper json (the attached file is not), a JSONFragmenter set to json["value"][*] with flattening enabled should give you what you want.

 

Hi @jdh - thanks for the feedback. I'm new to working with JSON so wasn't sure if this is proper json or not. I've tried your suggestion and this has definitely helped. I now have individual rows for each object with the response body for each contained in one attribute. I'll just need to work out how to split this in to individual attributes now. Thanks for your help.

 

jdh
Contributor
Forum|alt.badge.img+28
  • jdhContributor
  • Contributor
  • December 19, 2019
mattebridges wrote:

Hi @jdh - thanks for the feedback. I'm new to working with JSON so wasn't sure if this is proper json or not. I've tried your suggestion and this has definitely helped. I now have individual rows for each object with the response body for each contained in one attribute. I'll just need to work out how to split this in to individual attributes now. Thanks for your help.

 

In the JSONFragmenter, in the Flattening Parameters you should set Flatten Query Result into Attributes and Recursively Flatten Objects/Arrays both to Yes.

 

Then the attributes you need to use in the workspace you can expose.

mattebridges
Participant
Forum|alt.badge.img+5
  • mattebridgesParticipant
  • Author
  • Participant
  • December 19, 2019
jdh wrote:

Assuming the response is proper json (the attached file is not), a JSONFragmenter set to json["value"][*] with flattening enabled should give you what you want.

 

Thanks so much @jdh - that has worked brilliantly.

sigtill
Supporter
Forum|alt.badge.img+24
  • sigtillSupporter
  • Supporter
  • December 20, 2019

You can also use the FeatureReader and read the JSON directly. This will auto-populate the feature_types

The real SigTill

Reply


Helpful Members This Week

Community Stats

31,135
Posts
118,046
Replies
38,777
Members

Latest FME

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.