Extract values from a JSON string
Platform Notice: Cloud Only - This article only applies to Atlassian apps on the cloud platform.
Summary
Say you have a JSON string from which you’d like to extract specific values. This can be accomplished in Atlassian Analytics using a custom formula, adhering to SQLite syntax, and using the JSON_EXTRACT function presented in this article.
Non-nested JSON string
Nested JSON string
Another nested JSON string
Solution
Example to extract from a non-nested JSON string
Let’s say you have a table chart that lists the JSON strings, where each row corresponds to a different Jira work item’s description, as seen below:
If you wanted to get the version that each Jira work item belongs to, you would need to:
Add a Formula column and select Custom as the formula type
Use the
JSON_EXTRACTfunction
The general format of the function is like this:
JSON_EXTRACT("Description", '$.version')The function takes in two arguments separated by a comma:
The first argument is the column containing the JSON string. Make sure to wrap the column name in double quotes. In the example formula above, the column’s name is "Description."
The second argument is the path to the values that we’re trying to extract, wrapped in single quotes. The path starts with
$(which gives us access to the JSON row) followed by a period and the key of the value we want to pull (in other words, the label to the left of the value in the JSON string). Because we chose version, we extracted the value to the right of "version": in the string, giving us the output below:
From here, you can continue extracting other values you’d want. In our example, if you wanted to extract the Jira issue description type, we’d follow the same logic:
JSON_EXTRACT("Description", '$.type')Here’s our final table chart with the two extracted JSON values:
Extract from a nested JSON string
The JSON string wasn’t entirely non-nested in the previous example. If the values that we are looking to extract are nested, we have to change our approach a bit.
Let’s say you want to extract the text values from the Description column in the Issue table. The “text” JSON string is nested within an array (indicated by the square brackets).
To extract “text” from the “Description” column, you’d use the following function:
JSON_EXTRACT("Description", '$.content[0].content[0].text')If you were to only use the formula below, then the entire array (in other words, everything to the right of "content") would be returned.
JSON_EXTRACT("Description", '$.content')By appending [0] after content in our function’s path argument, we can pull the values from the first item in the content array. To extract the “text”, we again need to pull the values from the first item in the second content array, by appending [0] after the second content reference.
The full path argument '$.content[0].content[0].text' outputs the text Description information associated with each work item.
Another example to extract from a nested JSON string
Let’s look at another nested JSON string that looks something like this:
{
"user": [
{
"id": 1,
"gender": "Female",
"first_name": "Susan",
"last_name": "Huetson",
"email": "shuetson0@amazon.de",
"ip_address": "47.47.39.223",
"friend": [
{
"first_name": "Querida",
"last_name": "Clark"
}
]
}
],
"comment_id": 11111,
"comment": "This is my first comment!",
"post_id": 99999
}If you want to extract the first name of the user’s friend, you’d once again change the path argument in the “Formula column” step:
JSON_EXTRACT("JSON column", '$.user[0].friends[0].first_name')Let’s breakdown each part of the path argument:
The first part of the path,
$.user[0], brings in:{"id": 1,"gender": "Female", "first_name": "Susan", "last_name": "Huetson", "email": "shuetson0@amazon.de", "ip_address": "47.47.39.223", "friend": [{"first_name": "Querida", "last_name": "Clark"}]}You still need to move past the other bracket to the right of
"friend":, which is the next part of the function’s path.$.user[0].friend[0]brings in:{"first_name": "Querida", "last_name": "Clark"}Removing the brackets gives you access to easily extract the first name value. The final part of the path is calling on the key that contains this value (in other words,
first_name), which would be:$.user[0].friend[0].first_name
The output of the entire JSON_EXTRACT function would be Querida.
Was this helpful?