Question

Updating JSON - better solution

  • 23 April 2019
  • 6 replies
  • 16 views

Badge +7

Hi,

I just started working with JSON files and could use some help with a specific workflow. I have a list of addresses stored as JSON where I want to remove certain objects/arrays that match with IDs from a reference list. In the attached example I want to remove the address with the ID 1 from the JSON file. The workspace already works but I'm sure there must be a more elegant solution for what I am trying to to.

In my approach I expose the IDs with a ListExploder remove unwanted IDs via FeatureJoiner and then I reassamble the original JSON with a JSONTemplater/Aggregator/AttributeManager. It does the job but its not pretty. I already checked the very informative JSON tutorial but I could not apply it to my problem.

Thanks in advance for any tipps on how to improve my workspace.


6 replies

Badge +1

Yes, there is a really easy way. You can simply expose the SOURCE_ID and use a Joiner. I am "newish" to JSON as well but this seems to be the fastest way I can think of.

 

RemoveRowsUsingJSON.fmw

Badge +7

Yes, there is a really easy way. You can simply expose the SOURCE_ID and use a Joiner. I am "newish" to JSON as well but this seems to be the fastest way I can think of.

 

RemoveRowsUsingJSON.fmw

Thanks, do you have an idea on how to setup the JSON writer? The resulting json file should have the same schema as the input minus what is removed by the Joiner.

Badge +1

Thanks, do you have an idea on how to setup the JSON writer? The resulting json file should have the same schema as the input minus what is removed by the Joiner.

Absolutely! In FME the JSON writer is almost identical to the text writer. I updated the workspace to use a JSON Updater on the original JSON attribute. This allows the "Non-Joined" objects to simply be deleted at their JSON Index without altering the JSON format.

Since we do not alter the format we can just write out to a single text file. There is a lot more you can do with the JSON Updater than just delete so I would look into that. Also, if you don't need to, I would change your JSON reader to a text file reader and set it to read whole file at once.

RemoveRowsUsingJSON.fmw

Badge +7

Absolutely! In FME the JSON writer is almost identical to the text writer. I updated the workspace to use a JSON Updater on the original JSON attribute. This allows the "Non-Joined" objects to simply be deleted at their JSON Index without altering the JSON format.

Since we do not alter the format we can just write out to a single text file. There is a lot more you can do with the JSON Updater than just delete so I would look into that. Also, if you don't need to, I would change your JSON reader to a text file reader and set it to read whole file at once.

RemoveRowsUsingJSON.fmw

Wow, JSONUpdater is a game changer. This cut down processing time from 2.5 hours to 1.5 minutes :) Thank you very much for your support and ideas.

Badge +1

Wow, JSONUpdater is a game changer. This cut down processing time from 2.5 hours to 1.5 minutes :) Thank you very much for your support and ideas.

I am glad I can help and glad that is saved so much time!! I would look into the JSON Updater some more, also the JSON Templater is an amazing tool that might be able to do the same update without fragmenting the original record. But this seems to work!

Badge +3

Thanks @dellerbeck for this elegant solution.

I would like to underline the use of the JSONUpdater for this use case. You used this in your workspace but it wasn't that obvious in the description until deeper in the replies. Definitely for cases like this where there is a relatively complex JSON structure and you just want to insert, modify or delete an object or array element within it, JSONUpdater is often the most efficient way to go.

Here's a screen shot of some of the modification options from the JSONUpdater in your workspace:

Reply