Solved

Expose JSON Attributes

  • 10 June 2021
  • 7 replies
  • 42 views

Badge

Hi All,

I'm a bit behind the curve with JSON. Could somebody help describe the process to expose the three attributes in my simple file sample following:

 

{'items': [{'att1': 'abc',

  'att2': 'xyz',

  'att3': 123},

 {'att1': 'cde',

  'att2': 'tuv',

  'att3': 456}]}

 

Notice that 'items' holds all the attributes.

 

I have successfully setup a reader to intake the JSON, but now need to expose the attributes att1, att2, and att3. I looked at several transformers, but the parameters are somewhat foreign to me. A detailed workflow would be much appreciated, since some of the JSON lingo is foreign to me.

 

Thank you,

 

Tyler

FME Workbench 2021.0

icon

Best answer by hollyatsafe 15 June 2021, 01:30

View original

7 replies

Userlevel 6
Badge +31

Not sure what kind of output you expect, but I know two ways to do this:

{
"items": [{
"att1": "abc",
"att2": "xyz",
"att3": 123
},
{
"att1": "cde",
"att2": "tuv",
"att3": 456
}
]
}
  • JSONFragmenter
    • Query json["items"][*]
    • Flatten Query Result into Attributes = Yes
    • This returns a feature for each item with att1, att2, att3
  • JSONFlattener
    • This returns one feature with a list for each item with att1, att2, att3.
Badge

Not sure what kind of output you expect, but I know two ways to do this:

{
"items": [{
"att1": "abc",
"att2": "xyz",
"att3": 123
},
{
"att1": "cde",
"att2": "tuv",
"att3": 456
}
]
}
  • JSONFragmenter
    • Query json["items"][*]
    • Flatten Query Result into Attributes = Yes
    • This returns a feature for each item with att1, att2, att3
  • JSONFlattener
    • This returns one feature with a list for each item with att1, att2, att3.

Just looking for features.

Something like this (fid = feature id):

  fid att1 att2 att3

  1 abc xyz 123

  2 cde tuv 456

 

I don't understand the source on the JSONFragmentor and JSON Flattener transformers.  Why don't they use the input port as the source?

 

I played around with  JSON reader a bit more with your query (json["items"][*]) as food for thought and got closer, but the query is one level off.  I guess I need to get smart with JSON queries.  The reader seems like a requirement especially since I need HTTP authentication.

 

Tyler

Userlevel 6
Badge +31

Just looking for features.

Something like this (fid = feature id):

  fid att1 att2 att3

  1 abc xyz 123

  2 cde tuv 456

 

I don't understand the source on the JSONFragmentor and JSON Flattener transformers. Why don't they use the input port as the source?

 

I played around with JSON reader a bit more with your query (json["items"][*]) as food for thought and got closer, but the query is one level off. I guess I need to get smart with JSON queries. The reader seems like a requirement especially since I need HTTP authentication.

 

Tyler

What really helps with the queries is to save a sample of JSON you need and select that file in the JSONFragmenter. Then you can select the level you want in the navigation tree and it returns the query for you:

2021-06-11_06h24_02When you have the correct query switch back from File/URL to JSON attribute.

 

I think I never use the JSON reader. I always have the HTTPCaller to output JSON in the response and process it with the JSONFragmenter or the JSONExtractor.

Even if I have a JSON file I think I prefer to use the Text reader (read whole file at once) and process with the Fragmenter and Extractor.

Badge

Just looking for features.

Something like this (fid = feature id):

  fid att1 att2 att3

  1 abc xyz 123

  2 cde tuv 456

 

I don't understand the source on the JSONFragmentor and JSON Flattener transformers. Why don't they use the input port as the source?

 

I played around with JSON reader a bit more with your query (json["items"][*]) as food for thought and got closer, but the query is one level off. I guess I need to get smart with JSON queries. The reader seems like a requirement especially since I need HTTP authentication.

 

Tyler

@nielsgerrits​ , Great tip(s). I'll give this a try. If I stumble again, I'll post my workspace and a sample of JSON.

 

Tyler

 

Badge

Just looking for features.

Something like this (fid = feature id):

  fid att1 att2 att3

  1 abc xyz 123

  2 cde tuv 456

 

I don't understand the source on the JSONFragmentor and JSON Flattener transformers.  Why don't they use the input port as the source?

 

I played around with  JSON reader a bit more with your query (json["items"][*]) as food for thought and got closer, but the query is one level off.  I guess I need to get smart with JSON queries.  The reader seems like a requirement especially since I need HTTP authentication.

 

Tyler

Another fail.  I'm looking to return attributes att1 through att4, but extraneous json data is getting in the way.  My json["items"][*] query is grabbing hM, Im, os, and count instead of only those attributes (att1, att2, att3, and att4) under "items".  See results image:

fme_results1here is my query from the JSON Reader:

json_queryand here is a better representation of my JSON data:

{"items":[{"att1":"SCSC","att2":"SCSC","att3":"100900","att4":1},{"att1":"SCSC","att2":"SCSC","att3":"1055","att4":3}],"hM":true,"lm":2,"os":0,"count":2,"links":[{"rel":"self","href":"url/read/occu"},{"rel":"dscby","href":"url/met-cat/read/item"},{"rel":"first","href":"url/read/occu?lm=2"},{"rel":"next","href":"url/read/occu?os=2&lm=2"}]}

Any further tips would be appreciated.

 

Tyler

FME Workbench 2021.0

 

Badge +2

Just looking for features.

Something like this (fid = feature id):

  fid att1 att2 att3

  1 abc xyz 123

  2 cde tuv 456

 

I don't understand the source on the JSONFragmentor and JSON Flattener transformers. Why don't they use the input port as the source?

 

I played around with JSON reader a bit more with your query (json["items"][*]) as food for thought and got closer, but the query is one level off. I guess I need to get smart with JSON queries. The reader seems like a requirement especially since I need HTTP authentication.

 

Tyler

Hi @townest​ ,

 

When you set up the JSON Query in the Reader did you add a new Reader, or did you update an existing Reader?

 

Based on your screenshot, my guess is that you updating an existing Reader but did not update the Feature Type that was already on the canvas. When you are updating a Reader there is an 'Update Options' section that is set to Update the Reader only by default, however, you'll need to change this to 'Update Reader and Feature Type'.

 

With your sample JSON and the JSON Query set, the canvas should look like this:

 

Screen Shot 2021-06-14 at 4.28.42 PM 

Where the name of the Feature Type is what you have set in the Reader, and the attributes listed are those found specifically within the query set.

 

If this still isn't working, please could you share a copy of your workspace file and this should help with further troubleshooting.

Badge

Just looking for features.

Something like this (fid = feature id):

  fid att1 att2 att3

  1 abc xyz 123

  2 cde tuv 456

 

I don't understand the source on the JSONFragmentor and JSON Flattener transformers. Why don't they use the input port as the source?

 

I played around with JSON reader a bit more with your query (json["items"][*]) as food for thought and got closer, but the query is one level off. I guess I need to get smart with JSON queries. The reader seems like a requirement especially since I need HTTP authentication.

 

Tyler

@hollyatsafe​, using the 'Update Reader and Feature Type' fixed my issue. Problem resolved. Thank you. Tyler

Reply