Skip to main content
Solved

Read JSON file

magnus
Participant
Forum|alt.badge.img+2

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

Best answer by david_r

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.

View original
Did this help you find an answer to your question?

8 replies

david_r
Celebrity
  • david_rCelebrity
  • August 31, 2018

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.

magnus
Participant
Forum|alt.badge.img+2
  • magnusParticipant
  • Author
  • Participant
  • August 31, 2018

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.

david_r
Celebrity
  • david_rCelebrity
  • August 31, 2018
magnus wrote:

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.
magnus
Participant
Forum|alt.badge.img+2
  • magnusParticipant
  • Author
  • Participant
  • August 31, 2018

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

 

david_r
Celebrity
  • david_rCelebrity
  • Best Answer
  • August 31, 2018

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.

magnus
Participant
Forum|alt.badge.img+2
  • magnusParticipant
  • Author
  • Participant
  • August 31, 2018

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.

takashi
Influencer
  • takashiInfluencer
  • September 2, 2018
magnus wrote:

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.

 

 

magnus
Participant
Forum|alt.badge.img+2
  • magnusParticipant
  • Author
  • Participant
  • September 3, 2018
magnus wrote:

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.

 

 

Reply


Helpful Members This Week

Community Stats

31,224
Posts
118,407
Replies
38,827
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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