Using JSON Parser to Handle Keys With Periods

Last week, I was building a recipe in Workato using an HTTP connector and the JSON response format contained periods in the keys. Apparently, they did this instead of using nested JSON. I had never encountered that before but didn’t think much of it. When I went to use a datapill, I received a warning of “Please replace invalid datapill(s)”.

It’s odd because that warning didn’t prevent the recipe from running as it often does when there is an error with a datapill. The recipe ran but provided empty data, so if there were 500 records that I was trying to add to a lookup table, it would create 500 empty rows.

It would work if I used a ‘for each’ action and hardcoded the keys in formula mode like this.

However, I really wanted to use bulk actions which I couldn’t hard code (and even if I could, it would be a less than optimal solution).

The Solution: JSON Parser

I was working with Workato support and they recommended that I use the JSON Parser by Workato as a best practice. I had worked with the JSON Parser action before but not in the way that support recommended. 

In the ‘Sample document’ box, I added the JSON as it was except that I removed the periods. So if the key in the JSON was ‘course.name’, I would add it as coursename

In the ‘Document’ box, I used formula mode and put in the response datapill along with the formula of converting it to string, substituting periods with an empty value, changing hashes to colons, and substituting nil to null.

[Response | Step 4].to_s().gsub(‘.’,”).gsub(“=>”,”:”).gsub(‘nil’,’null’)

This would give me a JSON response I could work with so instead of taking the response from the actual action step, I took it from the JSON parser step and was able to use the bulk actions.