Parsing a text field with a JSON object

perla.miranda

Hi,

I created a custom text field (multiline text field) I'm using it to store a JSON object and I'd like to parse it to use the data. How can I parse this data with smart values in automation section?

For example,

• custom field name: tool_data
• The value that I'm putting in the tool_data custom field: [{"title": "header", "data_url": "https://www.url.com", "info": "Some other information"}, {"title": "header2", "data_url": "https://www.url2.com", "info": "Some other information 2"}]

I have this field in a parent task and I'd like parse the data and use it to create subtasks.

Darryl Lee

Unfortunately Automation does not have functions to parse JSON. It's ironic (and annoying) because it has lots of functions to format data into JSON.

Regrettably, if your data must be JSON, then you're going to have to rely on Text and List functions to parse out what you want. In my examples, my text field is named "Test Plan".

I was able to use the match function to create "lists" of each field, like so for "title":

{{issue.Test Plan.match(".+?\"title\": \"(.+?)\",")}}

This uses regular expression pattern matching to search for all the "title" elements in your text, and then save the values for those elements into a "collection".

When testing with your sample data, that returns:

header, header2

We could create similar lists for "data_url" and "info":

{{issue.Test Plan.match(".+?\"data_url\": \"(.+?)\",")}}

{{issue.Test Plan.match(".+?\"info\": \"(.+?)\"}")}}

So I've got three lists that I need to iterate with a counter that stops at the total number of elements in each list, but ooof, Automation does not make this easy.

Thankfully @[deleted]figured out how to create a counter in Advanced Branches so that we can loop the right number of times. (Once for each element in your JSON array.)

ANYWAYS It might be easier just to show you the rule I came up with:

1. Figure out how many elements are in your JSON array

I'm using the first query above and adding the .size function to get the a value that I am putting into the {{totalelements}} variable:

{{issue.Test Plan.match(".+?\"title\": \"(.+?)\",").size}}

2. Initialize the looping variable for the counter

We set it to NULL

 

3. Create the Advanced branch that counts from 0 to the {{totalelements}}

This is Bill's magic:

{{#varLoopValues.rightPad(varLoopValues.length.plus(totalelements), "X").remove("NULL").replaceAll("X","X,").substringBeforeLast(",").split(",")}}{{index}}{{^last}},{{/}}{{/}}

4. For each {{counter}}, create a Sub-task that "gets" that indexed element out of each list.

So one really annoying (and tricky to understand) thing about Automation is that the way it deals with variables within branches is unfortunately incredibly unpredictable.

The only sure-fire way I've been able to use "lists" is to recreate them within branches.

So that's why the Summary of the Sub-task here is:

{{issue.Test Plan.match(".+?\"title\": \"(.+?)\",").get(counter)}}

So we're generating that "collection" of titles, and then using the get function to extract the title that matches the index of the current {{counter}} value. We do the same for data_url and info, which I have just put into the Description:

Data: {{issue.Test Plan.match(".+?\"data_url\": \"(.+?)\",").get(counter)}}

Info: {{issue.Test Plan.match(".+?\"info\": \"(.+?)\"}").get(counter)}}

 

Whew. And that's it!

ANYways, I realize that's a lot to unpack. Please have a look, see if you can give it a try (and when you're ready for production, replace the Manual trigger with something more appropriate, like "Ticket Created", etc.)