Skip to main content

Hi

I am trying to read a JSON file:

{
  "meta" : {
    "Version" : "1.0",
    "Create time" : "2018-08-30 18:40:02 UTC"
  },
  "units" : [ {
    "dn" : "ou=test,ou=Org,o=xyz",
    "attributes" : {
      "countryName" : "SE",
      "createTimestamp" : "20180504113600Z",
      "creatorsName" : "cn=improxy,ou=System,o=test",
      "hsaCountyCode" : "14",
      "hsaIdentity" : [ "test" ],
      "hsaMunicipalityCode" : [ "1480" ],
      "modifiersName" : "cn=admin,ou=System,o=test",
      "modifyTimestamp" : "20180829082917Z",
      "ou" : [ "xyz" ],
      "vgrAo3kod" : [ "501" ],
      "vgrModifyTimestamp" : "20180618080954Z",
      "vgrModifyersName" : "cn=user,ou=Personal,o=test"
    }
  }, {
    "dn" : "ou=clients,ou=Org,o=test",
    "attributes" : {
      "createTimestamp" : "20180503113416Z",
      "creatorsName" : "cn=improxy,ou=System,o=test",
      "hsaIdentity" : [ "test" ],
      "modifiersName" : "cn=user,ou=System,o=test",
      "modifyTimestamp" : "20180504030003Z",
      "ou" : [ "clients" ]
    }
  }, {

It goes on for about 200 000 rows. I would like to extract some of data and test the quality of the address information (not featured in the example above, but available for other objects). I have found quite a few FME examples (such as read as text file and use JSONFlatener) of creating attribute data from JSON files, but I have so far been unable to manage to do it with this data set. Any suggestions on how to solve it? I use FME 2017.1.

Regards,

Magnus

Which part of the JSON is it that you need?

Either use the JSON reader, or alternatively a Text File reader (read all at once=Yes) together with a JSONFlattener:

Followed by a ListExploder on the JSON list "units":

Send the output to the Inspector, isolate the attributes you need and expose them in your workspace e.g. using the AttributeExposer before further processing.

Hi

Some objects have fields such as "PostalAddress" and "PostalCode", that´s what I am trying to access. I am not too familiar with JSON data and I am having trouble getting FME to show all available fields.

Hi

Some objects have fields such as "PostalAddress" and "PostalCode", that´s what I am trying to access. I am not too familiar with JSON data and I am having trouble getting FME to show all available fields.

You may have to manually expose some of the fields, try using the ListExploder and send to the Inspector (see my other post here) to see exactly what you need to expose.

All of the features are routed to the <Rejected> port in JSONFlattener.

The _json_error... reads "The JSON data is incomplete: Unexpectedly encountered the end of JSON data".

 

If you use the JSON reader you probably shouldn't be using the JSONFlattener.

Also, when creating the JSON reader, try the following query:

It should give you the following input feature type:

It may be that the reader schema is missing some attributes, this is because FME hasn't read the whole file to determine the complete schema (JSON is a variable schema format). But you can add those yourself with an AttributeExposer just after the reader. If you're unsure about what to type into the AttributeExposer, send the features to the Inspector, select a feature and look at the dynamic attributes listed in the Feature Information window.

Thanks, it´s getting closer. 35 attributes are now listed for the reader, but most of them are lost when I view the incoming data in a viewer, only 10 attributes remain there. Not sure why they are lost, but I´ll see if I can make the reappear.

Thanks, it´s getting closer. 35 attributes are now listed for the reader, but most of them are lost when I view the incoming data in a viewer, only 10 attributes remain there. Not sure why they are lost, but I´ll see if I can make the reappear.

Some attributes seem to be arrays. FME JSON reader reads JSON arrays as list attributes, and list attributes won't appear on the Table View in FME Data Inspector.

 

You can see details including list attributes for each feature with the Feature Information window, rather than the Table View.

 

 

Thanks, it´s getting closer. 35 attributes are now listed for the reader, but most of them are lost when I view the incoming data in a viewer, only 10 attributes remain there. Not sure why they are lost, but I´ll see if I can make the reappear.

Ah, thanks, that helped. Now, all of the relevant attributes are available.

 

 

Community Stats

32,043
Posts
121,684
Replies
39,664
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.