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 · Accepted solution]

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

[perla.miranda]

I'll give this a try!

[perla.miranda]

@Darryl Lee @Bill Sheboy is there any way that we can sort the counter from lowest to highest? It seems that the advanced branching for loop runs in any order.

[Darryl Lee]

Oh hey, I noticed that my two subtasks were created in reverse order.

Did you create more than 2? And when you say "in any order" you mean it wasn't always highest to lowest?

I'm guessing it's the unfortunate problem of Automation executing for each smart value asynchronously, or concurrently (both)?

Man, how long will we have to wait for https://codebarrel.atlassian.net/browse/AUT-517 which I guess is now probably superseded by https://jira.atlassian.com/browse/AUTO-32

But yeah, it's really weird and annoying that a "For each" loop would not process in order.

This has been discussed in depth here:

• Automation order of operations? 

And here:

• Automation for Jira - Create variable (New component) 

[Darryl Lee]

Ooof. Yes, totally different order across multiple runs. What fun! :-P

But ugh, that sucks. 

[perla.miranda]

@Darryl Lee any idea on why when using the size function it never returns 1, if size is one. It works if size is 2 or greater. Do you have this same issue? If not, I'll check if I just have something weird going on on my side.

[Darryl Lee]

Hey there, so yeah, what I forgot is that the match function will only create "lists" if there are multiple matches. In the case of only 1 item, it just returns a single string.

So then, we need to check if that's the case (using the isNumeric function), and if so, just create the one item. (Note that change to the smart value match function to grab Title, Data, and Info. We no longer are using get(counter) to indicate get a specific element of the list because well, there is no list.)

I was able to use an IF/ELSE block to allow the rule to continue in the case of more than one match.

However, you cannot put an Advanced Branch Block (which we're using to do the multiple subtask creation) inside of an IF/ELSE block. So while the rule above actually "works" (single and multiple subtasks will now get created properly), it will also throw an error for single items because when it gets to the last block, the {{counter}} value will be invalid without a {{varLoopValue}} defined.

I got this error:

The provided smart value was unable to be resolved to an object.

SO once you test and can trust that it's working, you could set the Notify on error option to be "Don't notify". That's not ideal though, because you would want to know if something odd happened in our fake JSON parsing.

Hrm. I guess we could put another standalone If: block before the Advanced Branch block to check if {{totalelements.isNumeric}} equals false, which should simply stop the rule if it evaluates true. Let me try that...

[perla.miranda]

ok this explains why! thank you! I was wondering if that was the case with match function but could not find any documentation. Thank you!

[Ignacio Pulgar]

Official solution by Atlassian:

https://confluence.atlassian.com/jirakb/querying-json-data-in-jira-automation-1319570559.html

[Will Sargent]

I'd like to also suggest that the incoming HTTP endpoint does a fantastic job of parsing JSON, so you could have Jira send an outgoing HTTP request to an AFJ webhook in another rule, and parse it that way using:

{{webresponse.body.mytopkey.level2}}

You can even get data from arrays using .get(0)

{{webresponse.body.mytopkey.myarray.get(0).name}}

That being said, I'd love to see the AFJ team add functions for moving a json array into a variable, that could be accessed the same way as webresponse.