+1I am using FME to extract API data and pull the JSON string into a SQL table. I am able to work with most of the tables but there are a couple of them that are nested and am having trouble getting to format properly.
I have attached how the results need to look and sample of the JSON results
I tried using JSON Fragmenter but i cannot get the results. Can someone advise the best way to get this.
Best answer by takashi
Hi @ayraju, basically you can use multiple JSONFragmenters to extract every content from each level in a nested JSON document.
However, according to the desired result table shown in your screenshot, the requirement is to extract keys of the JSON "fields" objects in the lowest level as attribute values. This isn't usual JSON flattening operation.
I would provide two different approaches to resolve the special requirement. One is using regular expression (StringSearcher), another is using XQuery (JSONiq extension) expression (XMLXQueryExtractor).
See these workspace examples. Both are created with FME 2018.1.0.2.
Hi @ayraju, basically you can use multiple JSONFragmenters to extract every content from each level in a nested JSON document.
However, according to the desired result table shown in your screenshot, the requirement is to extract keys of the JSON "fields" objects in the lowest level as attribute values. This isn't usual JSON flattening operation.
I would provide two different approaches to resolve the special requirement. One is using regular expression (StringSearcher), another is using XQuery (JSONiq extension) expression (XMLXQueryExtractor).
See these workspace examples. Both are created with FME 2018.1.0.2.
+1Hi @ayraju, basically you can use multiple JSONFragmenters to extract every content from each level in a nested JSON document.
However, according to the desired result table shown in your screenshot, the requirement is to extract keys of the JSON "fields" objects in the lowest level as attribute values. This isn't usual JSON flattening operation.
I would provide two different approaches to resolve the special requirement. One is using regular expression (StringSearcher), another is using XQuery (JSONiq extension) expression (XMLXQueryExtractor).
See these workspace examples. Both are created with FME 2018.1.0.2.
+11@takashi I was just going thru the answers for my current Question, i got your answer . similar to this answer.
what if the json objects are dynamic. i mean if the geometry objects(geojson) and stored in nested arrays. and json query string is different for each geometry. in my case there are many geometries are stored inside json file. and not in one location, all are in different nested arrays.
How can we extract all the geometries from different nested arrays?
+13@takashi I was just going thru the answers for my current Question, i got your answer . similar to this answer.
what if the json objects are dynamic. i mean if the geometry objects(geojson) and stored in nested arrays. and json query string is different for each geometry. in my case there are many geometries are stored inside json file. and not in one location, all are in different nested arrays.
How can we extract all the geometries from different nested arrays?
FYI: An answer to @fkemminje's question was provided here:
