What type of reader/transformer can i use to read this file? json reader cannot view file layout and fails.

The JSON reader (not ESRI JSON or GEOJSON) seems to handle it just fine.

 

 

However since the the primary node "result" has a list, you'll either want to use a listExploder immediately afterwards, or read it in as text (whole file) and use the JSONExtractor and/or JSONFragmenter to get just the parts of interest.

daveatsafe wrote:

Hi @aj,

In the Parameters for the JSON reader, set the Schema Scan Mode to JSON Query. In the space below, add the 'result' Feature Type, with the Query 'json["result"][*]'.

This will cause the reader to scan your JSON file, breaking the result list into the individual records, and extracting the attribute names for the source Feature Type definition.

That did the trick. Now the issue is if I reference the url directly to get this data.

 

 

https://dev45421.service-now.com/api/now/table/problem?sysparm_limit=1000

 

 

I am unable to get that data. What do I need to do to stream this url content into the json document stored locally that I was then able to parse and load into a sql server table?

 

 

aj wrote:

That did the trick. Now the issue is if I reference the url directly to get this data.

 

 

https://dev45421.service-now.com/api/now/table/problem?sysparm_limit=1000

 

 

I am unable to get that data. What do I need to do to stream this url content into the json document stored locally that I was then able to parse and load into a sql server table?

 

 

readjsonfromurl.fmwHi @aj,

 

I am attaching a workspace that uses the HTTPCaller to download to a temp file, then reads that temp file using a FeatureReader, with the JSON query from above.

 

 

The FeatureReader produces a schema feature as well, so we can set the writer to dynamic mode, which will use the schema feature to set the output schema. I just used CSV for writer - you can change this to your desired output.

 

 

The link you sent is protected, so I just used the download link from this page to test. You will need to set the proper link and authentication in the HTTPCaller transformer.

 

 

The temp file created from the path generated by the TempPathnameCreator is automatically cleaned up when FME finishes running the workspace.