Queries

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.

Properties

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.

Query Statement

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.

Query Parameters

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:

CODE

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.

REST

An example REST path with a Query Parameter could look like this:

CODE

/countries/:region?limit=:max_results

Here we have two parameters:

region
max_results

In contrast to JDBC/SQL Queries, these values do not need a type. They will always be URL-encoded and replaced.

XML responses

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.

Query Wildcards

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)
@space.id - the space ID of the current Confluence content (e.g. 12345678)
@space.name - the name of the current space
@content.type - the type of the current Confluence content (page or blogpost)
@content.version - the version of the current Confluence content (e.g. 42)
@content.id - the ID of the current Confluence content (e.g. 12345678)
@content.title - the title of the current Confluence content (e.g. Documentation)
@macro.id - the ID of the macro executing your Query (e.g. abc-123-def-456)
@user.displayName - the ID of the current Confluence content (e.g. Emily Smith)

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:

CODE

SELECT *
FROM customers
WHERE companyName = :@space.key

The same is true for REST Queries:

CODE

/rest/search?query=:@content.id

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.