A query fetches data from one of your datasources. Its properties largely depend on the type of datasource being used.
All queries consist of the following properties:
Depending on the type of the query, there are some more properties. Please refer to the sections below.
For JDBC datasources the following additional properties are available:
By default, all parameters in a query will become strings. For example:
SELECT Name, Population FROM Country WHERE Continent = :continent
This will become something like the following after the replacement took place:
SELECT Name, Population FROM Country WHERE Continent = 'Europe'
Now in certain cases this behaviour is not intended. Examples are:
That’s why we implemented the Parameter Types configuration option for queries. You can configure types for all your query parameters in the section "Parameter Types" by inserting their names and selecting their respective types from the dropdown. If parameters are not specified the default value will be “String”.
Currently supported parameter types:
... IN(1,2,3,4,5) ...
... IN('one','two','three') ...
SELECT :mycolumn ...where you simply want the value to be inserted as a constant.
SELECT Name, GovernmentForm, Region, HeadOfState FROM Country WHERE Continent = :continent; SELECT Name FROM Country INNER JOIN CountryLanguage ON Country.Code = CountryLanguage.CountryCode WHERE Language = :language; SELECT * FROM City WHERE Population > :population; SELECT Name, GovernmentForm, Region, HeadOfState FROM Country WHERE Population > :minPopulation AND Population < :maxPopulation; SELECT Name FROM Country LIMIT :max; -- "max" needs the parameter type "Integer" SELECT Name, GovernmentForm FROM Country WHERE Name IN (:names); -- "names" needs the parameter type "ListOfStrings"
For REST datasources the following additional properties are available:
JSON Path: Path to the result within the JSON response. JSONPath expressions refer to a JSON structure in the same way as XPath is used in combination with an XML document. The $-sign stands for the virtual root, which is also the default value, if the field is left empty. Starting from there, field names, wildcards and indexes are used to navigate deeper. The child operator can be a dot (e.g. $.store.book.title) or square brackets (e.g. $[‘store’][‘book’][‘title’]). The most important operates are the following one, you can find a more detailed explanation and some examples in this article on JSONPath by Stefan Goessner.
|. or ||child operator|
|*||wildcard for all elements regardless their names|
|[<integer i>]||array operator for element at position i|
|?(<boolean expr>)||applies a filter expression|
As mentioned above, both SQL queries and REST calls may include PocketQuery parameters. These parameters can be set when adding the PocketQuery Macro to a page which makes the query much more versatile.
PocketQuery parameters are marked with a colon in front of their name (:parameterName). For SQL queries, it is possible to set the type of the parameter to influence how the final values will be included in the SQL query. See the section Parameter Types above for more details. For REST calls there are no different parameter types - all parameter values will be URL encoded and then included in the REST call URL.
Instead of using fixed values, it is possible to use wildcards to set a PocketQuery parameter. These wildcards will be replaced by the actual content before the query is executed. The wildcards can be set by the user when adding the PocketQuery macro to a page, and since version 3.2.0 it is also possible to set wildcards directly within the query statement or REST URL itself. This way the macro user herself has no opportunity to adjust the values manually which can be important for security.
The following wildcards can be set as parameters values:
For users that are not logged into Confluence @username, @userfullname and @usermail will return "anonymous".
Note: When using a wildcard within the query statement or REST URL, it has to be prefixed with a colon to mark it as a parameter. So the resulting syntax is something like "SELECT * FROM mytable WHERE id LIKE :@pageid".
For each query you can decide whether its results should be added to the Confluence search index. They can then be found using the search bar on the top right, which makes content discovery much easier.
If the Index checkbox is checked, all results retrieved with this query will be added to the Confluence index when
One challenge using the Confluence search index for PocketQuery results is that changes are not detected by Confluence like they are with the content of Confluence pages: Confluence won’t recognize when something changes in your external database and the results of your macro change in consequence. In order to keep index data up-to-date, we implemented a Scheduled Job. Its task is to reindex all pages that contain a PocketQuery macro with a query that has indexing enabled. You can configure the job’s schedule at Confluence Admin > Scheduled Jobs:
The default schedule is to trigger the job every night at 1 AM. You might want to change this regarding your specific environment. Note that the job might be quite performance-intensive since it may iterate over a large set of pages, depending on how many PocketQuery macros you use. Also, these queries are run against your external database to retrieve the latest results. (Feel free to disable the job if you don’t want to make use of this feature at all.)
PocketQuery has a caching mechanism implemented that enables to cache the result of queries. This can reduce traffic with your datasource significantly. Without caching, every PocketQuery macro will trigger an interaction with the datasource when it is run (i.e. when a Confluence page with a macro is viewed).
For every query in the PocketQuery Admin, you can define how long the results of the query should be cached. When a PocketQuery macro with that query is run, it is checked if the result in the cache is older than the specified time, and only if so, the contents are refreshed by a new query. The cache duration can be specified in seconds (e.g. “45s”), minutes (e.g. “45m”), hours (e.g. “12h”) or days (e.g. “3d”).
You can clear the cache separately for every query by clicking at the “Clear cache” label above the cache setting (within the PocketQuery Administration when editing a query):
Since results from the database can become arbitrarily big, it is important to limit the cache size to some amount. We set a default to 100 items which means that only a total of 100 query results can live in the cache. You can administer that limit at Confluence Admin > Cache Management.
Click on “Show advanced view” on top of the list:
Locate the PocketQuery cache row and click on “Adjust size”. From time to time it might also be useful to flush the whole PocketQuery cache and start from scratch again. You can do so by clicking “Flush”.
Results in the cache are always identified by three parts:
More formally, each result gets a cache key by the following pattern:
This means, a new cache item is created when the PocketQuery macro is run if: