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.

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:

SELECT *
FROM countries
WHERE population > :min_population
CODE

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:

/countries/:region?limit=:max_results
CODE

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)

  • @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)

  • @macro.id - the ID of the macro executing your Query (e.g. abc-123-def-456)

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
CODE

The same is true for REST Queries:

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

Alternatively, wildcards can be used as values for a Query parameters. To do this, simply enter your desired wildcard via the PocketQuery macro editor.

Property Wildcards

Additionally to the standard wildcards mentioned above, PocketQuery Cloud now also supports property wildcards. Using these wildcards, you can obtain a content property value. They have prefix :@content.property., followed by your property key. For example, if we want to query a property called lively-apps-secret, we use the following wildcard: :@content.property.lively-apps-secret.

If you are interested in resource limits related to Queries, check out this article.