• Home
  • Forums
  • FME Form
  • General
  • How can i parse json from API responses into a tabular format (flatten)? I've got some progress based on the forums, but I'm doing a deeper query this time and struggling
Solved

How can i parse json from API responses into a tabular format (flatten)? I've got some progress based on the forums, but I'm doing a deeper query this time and struggling

  • 10 March 2021
  • 8 replies
  • 20 views
jefejiraffe

the basic gist I've gotten from the forums and examples is:

httpCaller>jsonFragmenter>jsonExtractor and I can make that work in a couple of simpler examples. My ignorance is showing when I get to a larger _response_body. I've attached an example json file that is the response for this specific API call, and I would just post my whole workbench but it includes an API key that I don't want to expose.

Thanks in advance for any guidance you can provide.

 

I'm basically trying to flatten the entire response into a table.

icon

Best answer by jdh 10 March 2021, 23:13

View original
1 Attachment

8 replies

jdh
Rank
Badge +22

Why not simply use a JSONFlattener? You would need to expose any attributes you want to manipulate on the canvas, but even unexposed they would be available on a writer in dynamic mode.

jefejiraffe

Why not simply use a JSONFlattener? You would need to expose any attributes you want to manipulate on the canvas, but even unexposed they would be available on a writer in dynamic mode.

I'm not fully understanding how to reference the attributes. I've been attempting, but my lack of familiarity with json is preventing me from adding the attributes properly. Can you give me an example? I've read the docs and the help, and additional json guidance, but I'm new to working with json. Can I parse the whole thing into a grid using only the HTTPcaller and the JSONflattener with the proper attributes? I think my challenge is the syntax for exposing attributes.

 

For example, this query is GET JOB DATA for a single job. At the top level, there are connections and nodes, and a literal string that is the key and value for the name of the job. I've been able to get some pieces flowing, but I can see my the ignorance that is limiting me is fully understanding json formatting (I'm trying :D !) and that would help me understand better the relationships within the document.

 

I sincerely appreciate the guidance. Would you provide perhaps an example of the syntax I would use to expose one or two of the keys within the Jsonflattener given the example_json? I'll crack the flattener back open and read more about exposing attributes.

 

Thank you very much for your response. 😀

jdh
Rank
Badge +22

I'm not fully understanding how to reference the attributes. I've been attempting, but my lack of familiarity with json is preventing me from adding the attributes properly. Can you give me an example? I've read the docs and the help, and additional json guidance, but I'm new to working with json. Can I parse the whole thing into a grid using only the HTTPcaller and the JSONflattener with the proper attributes? I think my challenge is the syntax for exposing attributes.

 

For example, this query is GET JOB DATA for a single job. At the top level, there are connections and nodes, and a literal string that is the key and value for the name of the job. I've been able to get some pieces flowing, but I can see my the ignorance that is limiting me is fully understanding json formatting (I'm trying :D !) and that would help me understand better the relationships within the document.

 

I sincerely appreciate the guidance. Would you provide perhaps an example of the syntax I would use to expose one or two of the keys within the Jsonflattener given the example_json? I'll crack the flattener back open and read more about exposing attributes.

 

Thank you very much for your response. 😀

I'm guessing you want each object under the nodes to be a separate feature, and the first attribute name (ex. -MTkGZlFwcYOvnj8AIvU) should be a value for an id field?

jefejiraffe

I'm not fully understanding how to reference the attributes. I've been attempting, but my lack of familiarity with json is preventing me from adding the attributes properly. Can you give me an example? I've read the docs and the help, and additional json guidance, but I'm new to working with json. Can I parse the whole thing into a grid using only the HTTPcaller and the JSONflattener with the proper attributes? I think my challenge is the syntax for exposing attributes.

 

For example, this query is GET JOB DATA for a single job. At the top level, there are connections and nodes, and a literal string that is the key and value for the name of the job. I've been able to get some pieces flowing, but I can see my the ignorance that is limiting me is fully understanding json formatting (I'm trying :D !) and that would help me understand better the relationships within the document.

 

I sincerely appreciate the guidance. Would you provide perhaps an example of the syntax I would use to expose one or two of the keys within the Jsonflattener given the example_json? I'll crack the flattener back open and read more about exposing attributes.

 

Thank you very much for your response. 😀

Yes, that is correct. The Job has an ID, that would be the top level. I'd need column two to be perhaps object type, and that would be nodes and connections. I don't know why I didn't link this. I have the schema for a job. https://katapultpro.com/schema/job/

connections and nodes do have just those json indexes for names, but the job actually has a ["name"] key. Thanks a lot for taking the time to look at this. I'm getting a swift education and I'm eager to understand the structure and referencing.

jdh
Rank
Badge +22

I'm not fully understanding how to reference the attributes. I've been attempting, but my lack of familiarity with json is preventing me from adding the attributes properly. Can you give me an example? I've read the docs and the help, and additional json guidance, but I'm new to working with json. Can I parse the whole thing into a grid using only the HTTPcaller and the JSONflattener with the proper attributes? I think my challenge is the syntax for exposing attributes.

 

For example, this query is GET JOB DATA for a single job. At the top level, there are connections and nodes, and a literal string that is the key and value for the name of the job. I've been able to get some pieces flowing, but I can see my the ignorance that is limiting me is fully understanding json formatting (I'm trying :D !) and that would help me understand better the relationships within the document.

 

I sincerely appreciate the guidance. Would you provide perhaps an example of the syntax I would use to expose one or two of the keys within the Jsonflattener given the example_json? I'll crack the flattener back open and read more about exposing attributes.

 

Thank you very much for your response. 😀

Have a look at the attached workspace, I only parsed out the nodes figuring you would want to treat them separately from the connections, and only exposed a few attributes. The renamers are optional, and I would probably also strip out the ".button_added" with another bulk attribute renamer.

jefejiraffe

I'm not fully understanding how to reference the attributes. I've been attempting, but my lack of familiarity with json is preventing me from adding the attributes properly. Can you give me an example? I've read the docs and the help, and additional json guidance, but I'm new to working with json. Can I parse the whole thing into a grid using only the HTTPcaller and the JSONflattener with the proper attributes? I think my challenge is the syntax for exposing attributes.

 

For example, this query is GET JOB DATA for a single job. At the top level, there are connections and nodes, and a literal string that is the key and value for the name of the job. I've been able to get some pieces flowing, but I can see my the ignorance that is limiting me is fully understanding json formatting (I'm trying :D !) and that would help me understand better the relationships within the document.

 

I sincerely appreciate the guidance. Would you provide perhaps an example of the syntax I would use to expose one or two of the keys within the Jsonflattener given the example_json? I'll crack the flattener back open and read more about exposing attributes.

 

Thank you very much for your response. 😀

Thank you very much. I'm using FME desktop 2018 and 2020, and I'm not allowed to install the latest and greatest. I'm getting a warning about JSONFragmenter (version8) and JSONExtractor (version 5) and when I open the workbench, those two transformers are missing. I could probably understand and emulate what you've done if I had even an image of the transformers. I'm sorry to be a pain, i don't know how to get beyond this version issue.

jdh
Rank
Badge +22

I'm not fully understanding how to reference the attributes. I've been attempting, but my lack of familiarity with json is preventing me from adding the attributes properly. Can you give me an example? I've read the docs and the help, and additional json guidance, but I'm new to working with json. Can I parse the whole thing into a grid using only the HTTPcaller and the JSONflattener with the proper attributes? I think my challenge is the syntax for exposing attributes.

 

For example, this query is GET JOB DATA for a single job. At the top level, there are connections and nodes, and a literal string that is the key and value for the name of the job. I've been able to get some pieces flowing, but I can see my the ignorance that is limiting me is fully understanding json formatting (I'm trying :D !) and that would help me understand better the relationships within the document.

 

I sincerely appreciate the guidance. Would you provide perhaps an example of the syntax I would use to expose one or two of the keys within the Jsonflattener given the example_json? I'll crack the flattener back open and read more about exposing attributes.

 

Thank you very much for your response. 😀

Which are of course, the two most important transformers.

The JSONExtractor is easy, just make a target attribute of name with a query of json["name"]

 

For the JSONFragmenter see this image.

KB_JSONFragmenter

jefejiraffe

I'm not fully understanding how to reference the attributes. I've been attempting, but my lack of familiarity with json is preventing me from adding the attributes properly. Can you give me an example? I've read the docs and the help, and additional json guidance, but I'm new to working with json. Can I parse the whole thing into a grid using only the HTTPcaller and the JSONflattener with the proper attributes? I think my challenge is the syntax for exposing attributes.

 

For example, this query is GET JOB DATA for a single job. At the top level, there are connections and nodes, and a literal string that is the key and value for the name of the job. I've been able to get some pieces flowing, but I can see my the ignorance that is limiting me is fully understanding json formatting (I'm trying :D !) and that would help me understand better the relationships within the document.

 

I sincerely appreciate the guidance. Would you provide perhaps an example of the syntax I would use to expose one or two of the keys within the Jsonflattener given the example_json? I'll crack the flattener back open and read more about exposing attributes.

 

Thank you very much for your response. 😀

Thanks a lot! The attributes formatting took me to the finish line. I am able to adapt this to my existing workbench. I really appreciate the education. I'll be sure to pay it forward.

Reply


Community Stats

28,670
Posts
109,366
Replies
36,531
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.