Skip to main content
  • EN

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

 

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.

     

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

     

    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.

    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.

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

    Reply


    Community Stats

    31,784
    Posts
    120,736
    Replies
    39,401
    Members

    Latest FME

    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.