Queries are a type of PocketQuery Entity that defines what data shall be fetched from a given Datasource. They can be executed using the PocketQuery macro. Each Query can also be assigned a Template and a Converter for changing how its result is displayed in a Confluence page.
All queries consist of the following properties:
Name: The name identifying the query.
Datasource: The datasource against which the query is being run (referencing a datasource entity).
Template: A template used to display the result of the query in the Confluence page containing the PocketQuery macro. References a template entity. If the Default template is selected, a default PocketQuery template is used.
Converter: Converter used to transform the response of the datasource before it is handed on to the template. See the “Converters” section.
Spaces: Spaces this query should be available in. If no space is selected, the query can be executed in any space.
Depending on the type of the query, there are some more properties. Please refer to the sections below.
As Datasources can be either JDBC databases or REST APIs, there’s also two types of Queries:
If a Query belongs to a JDBC Datasource, it will require you to enter a SQL statement.
If a Query belongs to a REST Datasource, it will require you to enter a Path that will be appended to the Base URL of the REST API.
PocketQuery allows you to add parameters to your Query Statement. This can be done via the
:parameter-syntax. Users can later define values for parameters in the PocketQuery Macro.
JDBC / SQL
An example SQL statement with a Query Parameter could look like this:
SELECT * FROM countries WHERE population > :min_population
Here we have a single parameter called
min_population. Notice how we except it to be of type
Integer as we are using the
> operator on it. For this reason, you will need to set the type of this parameter to
Integer under the Advanced Settings in your Query.
If you want to see an example of how this works, please refer to this part of our Getting Started guide.
An example REST path with a Query Parameter could look like this:
Here we have two parameters:
In contrast to JDBC/SQL Queries, these values do not need a type. They will always be URL-encoded and replaced.
PocketQuery can also understand XML responses coming from REST Datasources. If PocketQuery receives XML it will try to automatically convert it into JSON before processing it further. Enable the debug mode of your macro to see details about the transformation.
Instead of using fixed values, it is possible to use wildcards to set a PocketQuery parameter. These wildcards will be replaced by a given value from the Query context before the Query is executed. The wildcards can be set by the user when adding the PocketQuery macro to a page, or 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 desirable for security.
The following wildcards can be set as parameters values:
@space.key- the human readable space key of the current Confluence content (e.g.
@space.id- the space ID of the current Confluence content (e.g.
@content.type- the type of the current Confluence content (
@content.version- the version of the current Confluence content (e.g.
@content.id- the ID of the current Confluence content (e.g.
@content.title- the title of the current Confluence content (e.g.
@macro.id- the ID of the macro executing your Query (e.g.
@user.displayName- the ID of the current Confluence content (e.g.
Wildcards can be used in two different ways. For one, they can be part of the Query statement directly. In this case, the wildcard needs to be prefixed by a
: like a normal parameter:
SELECT * FROM customers WHERE companyName = :@space.key
The same is true for REST Queries:
Alternatively, wildcards can be used as values for a Query parameters. To do this, simply enter your desired wildcard via the PocketQuery macro editor.
If you are interested in resource limits related to Queries, check out this article.