[Advanced] Combining results from multiple REST calls

Sometimes, we receive results from a REST endpoint containing keys (IDs, codes, etc.), and the like. But rather than displaying these cryptic keys, we would like to display readable names. Unfortunately, these names can sometimes only be retrieved by sending another REST call to ask for a “name by key”. Luckily, this can be achieved with PocketQuery by using its executeQuery template helper.

Be careful with this example and think carefully about what you are doing! If used wrongly, this example can invoke arbitrarily many REST calls with negative impact on your Confluence instance and you may suffer bad reputation on the side of the REST API. Make sure to make use of PocketQuery’s caching feature as described below. If you have any question, please reach out to us!

We will use the “REST Countries” API for this example: https://restcountries.eu/. We will retrieve a list of countries from this API, each of them having a country code. For each of these codes, we will call another REST endpoints to ask for the country name by using the code.

Setting up the Datasource

You’ll only need one datasource for the REST Countries API:

Name: REST Countries (this is your choice)
Type: REST Basic
URL: https://restcountries.com/v3.1

Click on “Test Connection” and you should see this:

Setting up the main Query and a Converter

We need to create the main query first that asks the REST API for the list of countries:

Name: REST Countries by Continent
Datasource: REST Countries (the datasource we created in the previous step)
REST URL: region/Europe (note: you could also make the continent a query parameter and make it dynamic)

Let’s quickly test this by clicking on “Test Query”. This should already produce a result:

You might now spot that this REST API already returns a name. We’ll pretend that it doesn’t by creating a converter and only including a set of columns (and exclude the name column). We’ll name the converter also REST Countries by Continent. This is the converter code:

CODE

function convert(json) {
  return JSON.parse(json).map(country => {
	return {
	  'Flag': country.flag,
	  'Name': country.name.common,
	  'Code': country.cca2,
	  'Capital': country.capital && country.capital[0],
	  'Population': country.population
	}
  });
}

We will provide 4 columns: Code, Capital, Population, Area. The value for each column, we’ll read from the result.

Now, let’s assign the new converter to the query:

If we now test the query again, the result should look like this:

Now, we don’t have a name anymore, but only a code. For each of these rows, we’ll now want to ask another REST endpoint for the country’s name.

Setting up the Nested Query and a Converter

We’ll now set up a second query for asking the REST API for a country’s name, given its code. We create a new query:

Name: REST Country by Code
Datasource: REST Countries
REST URL: alpha/:Code

If we now “Test Query” we’ll have to provide a country code. A valid country code example is CA for Canada. If we preview this query we’ll see an error (enable the “Debug” toggle to see this output):

The result from the REST endpoint is an object, but PocketQuery expects a list/array. We’ll need to create another Converter to make it an array (with only one item). We name the Converter REST Country by Code (just like the query). It has this content:

CODE

function convert(json) {
  var parsed = JSON.parse(json);
  return [{ Name: parsed.name }];
}

As a result, the result of this query will have one single item with a Name. We now assign this Converter to our REST Country by Code Query:

Now, if we click “Test Query” (supplying CA as parameter and clicking “Preview”) we should see this:

Combining the two Queries

Now we have two Queries and each of them has its own Converter. What we’ll want to do now is the following:

Execute REST Countries by Continent
For each country,
- execute REST Country by Code and save the country’s name in a variable
- render the country’s details from the main query AND the country’s name from the variable

For this purpose, we’ll need to create a Template. But first, let’s remember the Query Key of REST Country by Code because we’ll need this for executing the query from within the template:

Now let’s create a Template that we’ll also name REST Countries by Continent. The content may look a bit intimidating at first, but in the end it’s not so bad:

CODE

## Relpace QUERY_KEY with the UUID of your nested query
## You can find the UUID under the name of the query in the query form
#set($queryKey = "QUERY_KEY")

<table class="aui confluenceTable pocketquery-table">
  <thead>
    <tr>
      <th>Name</th>
      <th>Capital</th>
      <th>Population</th>
      <th>Area</th>
    </tr>
  </thead>
  
  <tbody>
    #foreach ($row in $result)
      #set($nameQueryResult = $PocketQuery.executeQuery($queryKey, {
        "Code": $row.Code
      }))
      <tr>
        <td>$nameQueryResult.get(0).Name</td>
        <td>$row.Capital</td>
        <td>$row.Population</td>
        <td>$row.Area</td>
      </tr>
    #end
  </tbody>
</table>

First, we create the table headings (<th> elements) Name, Capital, Population, and Area. Then, in the table body, we iterate over the result of our main query (REST Countries by Continent). And now it gets interesting: we call our nested query (REST Country by Code) using the $PocketQuery.executeQuery helper method. We provide it the query key that we copied in the previous step and an object containing the query parameters. In our case, this is the country code, that we take from the $row object we’re currently iterating over. We save the result of the nested Query in a variable called $nameQueryResult.

Now, we can create the columns for each row (<td> elements). The first one should contain the country name which we now take from the result of the nested query. Since the result will be an list with only one element, we’ll need to extract the only element with get(0). On this object we can now read the Name that we specified in the REST Country by Code Converter. Let’s save this Template and set it on our REST Country by Code Query:

If we now test the query again, we’ll get names instead of codes:

This is exactly what we wanted 🎉

Use Caching for Queries

Although we already achieved what we wanted, we have a bit of a problem now: each time a user loads a page with this query, there will always be 1 + NUMBER_OF_ROWS REST calls! This can be a huge performance problem and the REST API might even block or rate limit you! To avoid this problem, we can cache the query results.

First of all, let’s increase the maximum cache size for the “PocketQuery Results” cache. Go to “Confluence Administration > Cache Management > Show advanced view”. Find the cache with name “PocketQuery Result Cache” and click on “Adjust size”:

Let’s not be nitpicky here and just give it a size of 1000 and click on “Submit”.

Now let’s go back to our two queries REST Countries by Continent and REST Country by Code and specify a value of 10d, meaning “10 days”. This means that the cache will only become stale after 10 days (feel free to adjust this value to your liking):

Note that you can clear these caches anytime by clicking on the “Clear cache” link!

Sorting the result

There are different ways how you can sort your results. The simplest is to do so in your converter, using JavaScript’s sort function. Here’s a changed version of the above converter, sorting our result by the country’s capital:

CODE

function convert(json) {
  var parsed = JSON.parse(json);
  var result = parsed.map(function(country) {
    return {
	  'Code': country.cca2,
	  'Capital': country.capital[0],
	  'Population': country.population
      'Area': country.area
    };
  });
  result.sort(function(country1, country2) {
    return country1.Capital.localeCompare(country2.Capital);
  });
  return result;
}

In this case, we’re using JavaScript’s String.localeCompare function for the sorting. This works for strings only. If we wanted to sort by Population, a number, we could apply the sort function like this instead:

CODE

result.sort(function(country1, country2) {
  return country1.Population - country2.Population;
});

Note that you’ll also be able to sort the table in the UI by clicking on the table heading you want to sort on.

Summary

In this article, we showed you how to combine the results of multiple queries to form one single result. We know that this article may look quite intimidating, so please don’t hesitate to reach out to us if you have trouble converting this example to your own use case!