Extract values from a JSON string

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 theJSON_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 issue description:

If you wanted to get the version that each Jira issue belongs to, you would need to:

1. Add a “Formula column” step.

2. Select Custom as the formula type.

3. Use the JSON_EXTRACTfunction.

The general format of the function is like this:

The function takes in two arguments separated by a comma:

1. The first argument is the column containing the JSON string. Make sure to wrap the column name in double quotes. In our example, the column’s name is “Description”.

2. 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 into 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:

Here’s our final table chart with the two extracted JSON values:

An example to 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 Jira 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:

If you were to only use…

…this would return the entire array (in other words, everything to the right of "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:

The full path argument '$.content[0].content[0].text' outputs the text information in each row:

Here’s our final table chart with the extracted JSON values:

Another example to extract from a nested JSON string

Let’s look at another nested JSON string that looks something like this:

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:

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.