Extract and report on JSM Approvers data

[nclark]

Hi, I'm working on a dashboard for a service project that has approvals configured in the workflow, and we'd like to report on the time it takes for approvals to be received by each approver.

I've found that the approvals data we need is stored in a custom field in jira_issue_field where the name is 'Approvals'. This data is stored as a string, however it appears to have a json structure to it. Any suggestions on how to extract the data? I feel like i need to map it to a json structure and then "explode" the data somehow into multiple rows, but still trying to figure out how to do that exactly.

[Tejaswi G]

Hi @nclark

Thank you for reaching out to Analytics community support. 

You can use the JSON_EXTRACT function and get the desired value from your custom fields.

JSON_EXTRACT("Description", '$.content[0].content[0].text')

Please feel free to open a ticket with us if you face any issues with this extract.

Kind Regards,
Tejaswi