Featured image on how to use the JSON Parser in Workato to handle a JSON response with periods in the keys

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)”.

Screenshot of Workato datapill warnings 'Please replace invalid datapill(s) for two input boxes

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.

Screenshot showing two formulas. In the Status input box, we are taking the Step 7 output and then using ["couse.status"]. In the 'Register Date' input box, we are taking the Step 7 output and then using ["user.register_date"]

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’)

Screenshot of the input boxes for the JSON Parser action in Workato. In the 'Sample document' box there is a JSON response with all of the periods stripped out. In the document box there is the JSON response datapill from step 3 and a formula of .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.

Similar Posts