Solved

Need help Fragmenting JSON File with Nested Objects and Arrays


Badge +1

I am trying to create features out of a large json file that has many objects mixed in with arrays. I can almost get what I need by using the following query:

json["Domain"]["Classifications"][*]

Unfortunately, it leaves out a crucial attribute. Here is a screenshot of the json file where it has me stumped:

Pset_JsonfileHere is a link to the entire file.

The attribute underlined in red is what I am looking for. There are hundreds of them at this level, all differently named. I have not yet found an example of this schema type, with hashes within a hash. In addition, the schema is not uniform. I can get to where it will separate the "Psets" attribute into features, but the "IfcActuator" level does not come through unless it is all in one big feature. Here are some screen shots of what I mean:

JSON_1WildThis is the result of: json["Domain"]["Classifications"][*]

 

Add one more wildcard [*], and you get this:

JSON_2WildWhat syntax/language should be used to query json in FME?

I have been looking at JMESPath which seems close, but I have no idea.

 

Any insight is appreciated.

 

 

icon

Best answer by ctredinnick 13 May 2022, 09:45

View original

3 replies

Userlevel 3
Badge +16

Half the trouble with json like this is deciding what data you want out of it, to identify what the 'features' are that're required for your use.

I typically use combinations of the JSONFragmenter and JSONExtractor to break down and pull out what I need. For that attribute you're wanting, you can fragment up to it, and it'll be named as 'json_index' on the resulting feature. Rename it to save it as something, then you can fragment the rest of the json further from there as needed

image

Badge +1

Half the trouble with json like this is deciding what data you want out of it, to identify what the 'features' are that're required for your use.

I typically use combinations of the JSONFragmenter and JSONExtractor to break down and pull out what I need. For that attribute you're wanting, you can fragment up to it, and it'll be named as 'json_index' on the resulting feature. Rename it to save it as something, then you can fragment the rest of the json further from there as needed

image

Very interesting. I will try that and do a final post. Is the reader you show JSON-specific, or …a text reader? Does it matter?

Also, it looks like you didn’t use any fancy query language. Is that because we are looking for every attribute in this case, or have you found that FME transformers can do it all?

Badge +1

Here's a what I came up with. It got a little out of hand! The idea was to group by classification so it could be used to create IFC files from scratch using IfcOpenShell. Thanks for the tips!Pset_JsonfileFINAL

Reply