• EN
Question

Parse JSON from Bentley OpenGround

  • 26 April 2024
  • 4 replies
  • 42 views
benicholls
Rank
Badge +1

I would like to be able to parse a response from the Bentley OpenGround REST API, and am getting somewhere but not far enough!

I can use a mixture of attribute exposer and JSONFragmenter to get some information out, but it never quite looks like the table i want. i will be doing multiple queries over the coming months into this api, and will have a different number of columns each time, so cant parse through each data record based on the number of columns i want in that particular query! 

I can get to the point where i have a feature for every feature in the database, and then i have a _response_body which i need to parse further. i can keep parsing it through jsonfragmenter, and i end up with a jumbled mess where columns are names wrong and the colunn information i want is in the wrong part of the table. it is a simple structure, such as “DataFields” “heade” for column name and “value” for data itself. i will add some screenshots to aid in query. any help would be greatly appreciated!

Thanks

Ben

screenshot showing raw response for one attribute:
 

 

 

which i can parse with the json fragmentor, to get this, 

 

and then i try to expose the attributes as follows, but the results are the wrong structure - columns like LocationDetails.LocationID with a value of “BS***” etc

 

4 replies

nielsgerrits
Rank
Userlevel 6
Badge +33

Not sure if this is the best way, but how I do this:

  • Get a fragment for each feature.
  • Generate an ID for each feature. Can be done with a counter.
  • Fragment to the deepest array you need. json[*]["Datafields"][*] This creates a feature for each attribute.
  • Expose Header & Value.
  • Create an attribute with Header (@Value(Header)) as attribute name and Value as attribute value. This is a dynamic created attribute, so it will be unexposed.
  • Expose the attributes you created.
  • Remove unneeded attributes
  • Merge the attributes back into one feature using the Aggregator, group by the id generated at the start.

 

1 Attachment
benicholls
Rank
Badge +1

this is so cool! thanks so much. ill give it a go now! 

 

thanks again

ben

benicholls
Rank
Badge +1

H i there, 

I gave this a good go, and adjusted the input data so it more accurately reflected the data i am getting back from the server. 

i would love that the output has the GUID and then all of the attributes associated with that GUID

not have to manually expose each attribute - there are lots of attributes in the database, and i dont want to have to expose each one. not a proiblem if this is a necessary evil, fme is not magic!

your method worked but only for one feature, i assume that this is because the grouping can not be performed on the right attribute. i think the counter situation is weird and ultimately want to be able to use the GUID from the database itself if at all possible.

1 Attachment
nielsgerrits
Rank
Userlevel 6
Badge +33

In this case, you want to follow the next steps:

  • Fragment to objects. The things with the attributes Id, Group, HasDocuments. Use json[*].
  • Extract the attributes (Id, Group, HasDocuments).
  • Fragment to key-values pairs.
  • Create attributes from key-value pairs.
  • Merge key-value attributes, group by Id, Group, HasDocuments.

I edited your workspace demonstrating this. See attachment.

 

It is possible to work around manually exposing the attributes for some cases, like dynamic writing with a schema feature. The SchemaScanner generates the Schema Feature from the data. But this is more advanced stuff.

1 Attachment

Reply


Community Stats

28,788
Posts
109,752
Replies
36,610
Members
Terms & ConditionsCookie 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.