Confluence Query Language (CQL) allow you to create custom CQL Functions that perform advanced searches for content in Confluence. Create and share custom CQL functions with your users in order to empower their search.

Your search results will take the same form as the Content model returned by the Content REST API. Some examples are:

Read more about CQL Functions in the Confluence CQL Function Module documentation.

Custom CQL Functions

To start, let?s create a simple CQL Function that is going to return all attachments within a specified space. To create a custom CQL Function go to Admin ? Script CQL Functions, click on the Custom CQL functions link:

space attachments query

Fields available for a Custom CQL Functions are:

  • Note: optional field, used only for your reference and not used internally

  • Name: mandatory field used as the CQL Function display name. It should respect the list of reserved words and characters

  • Number of parameters: accepted by the CQL Function. In the example we define 1 parameter because we are expecting the user to provide the space key

  • Type: specifies if the CQL Function returns a single string value or a list of strings. (In the example we’re using 'Multi Value' because the function returns a list of attachments)

    • Single value: Ths type should return a single String value which will be the value that the Query Function will be tranformed into, appearing as a single quoted value following the = operator in the CQL statement being executed.

    • Multi value: This type should return an Iterable of String values which will be the values that the Query Function will be transformed into, appearing within the parenthesis following the IN clause in the CQL statement being executed.

  • Script file: path to the script accessible on the server

  • Inline script: the core of the function where we define what the CQL Function returns based on the input

Once the function is created it’s possible to retrieve the output using the ScriptRunner macro 'CQL Search'. Using the query 'title in spaceAttachments("ds")' here’s what the new custom CQL Function returns:

space attachments result

Binding Variables

There are two binding variables available in the CQL Function script:

  • params : Parameters passed into the Query Function in the CQL statement being executed

  • context : The class definition of the object that is passed as the context argument to the invoke method of a Query Function

Example CQL Functions

All the examples are available under Admin ? Script CQL Functions → Custom CQL functions → Expand examples section.

Search Page By Label

This CQL Function returns all the pages that contain the Label specified

Configuration
search page by label config
Inline script
import com.atlassian.confluence.labels.Label
import com.atlassian.confluence.pages.Page
import com.atlassian.confluence.pages.PageManager
import com.atlassian.confluence.spaces.Space
import com.atlassian.confluence.spaces.SpaceManager
import com.atlassian.sal.api.component.ComponentLocator

def spaceManager = ComponentLocator.getComponent(SpaceManager)
def pageManager = ComponentLocator.getComponent(PageManager)
String label = params[0] (1)
def ids = []

for (Space space : spaceManager.getAllSpaces()) { (2)
    for (Page page : pageManager.getPages(space, true)) { (3)
        for (Label pageLabel : page.getLabels()) { (4)
            if (pageLabel.getName().equalsIgnoreCase(label)) {
                ids << String.valueOf(page.getId()) (5)
            }
        }
    }
}
return ids
1 - Getting the label input value
2 - Iterating over all spaces
3 - Iterating over all pages
4 - Iterating over all labels
5 - Adding the page id if a label matches the input

It’s possible to use this function with the ScriptRunner macro 'CQL Search' providing the query 'content in pagesWithLabel("finance")'

Linked Pages

This CQL Function returns all the linked pages associated with the specified page

Configuration
linked pages config
Inline script
import com.atlassian.confluence.links.OutgoingLink
import com.atlassian.confluence.pages.Page
import com.atlassian.confluence.pages.PageManager
import com.atlassian.confluence.spaces.Space
import com.atlassian.confluence.spaces.SpaceManager
import com.atlassian.sal.api.component.ComponentLocator

String spaceKey = params[0] (1)
String pageTitle = params[1]

SpaceManager spaceManager = ComponentLocator.getComponent(SpaceManager)
PageManager pageManager = ComponentLocator.getComponent(PageManager)
def ids = []

Space space = spaceManager.getSpace(spaceKey)

for (Page page : pageManager.getPages(space, true)) { (2)

    for (OutgoingLink link : page.getOutgoingLinks()) { (3)
        if (link.getDestinationPageTitle().equals(pageTitle) && link.getDestinationSpaceKey().equals(spaceKey)) { (4)
            ids << String.valueOf(page.getId())
        }
    }
}

return ids
1 - Getting the space key and the target page title from the inputs
2 - Iterating over all pages in the space
3 - Iterating over all outgoing links from a page
4 - Adding the page id if the outgoing link matches both the space key and the target page title

It’s possible to use this function with the ScriptRunner macro 'CQL Search' providing the query 'content in linkedPages("ds", "Welcome to Confluence")'

Add-on Pages

This CQL Function returns all the pages that are using any macro of a specified add-on

Configuration
add on pages config
Inline script
import com.atlassian.confluence.macro.browser.MacroMetadataSource
import com.atlassian.confluence.pages.Page
import com.atlassian.confluence.pages.PageManager
import com.atlassian.confluence.plugin.ConfluencePluginManager
import com.atlassian.confluence.search.v2.*
import com.atlassian.confluence.search.v2.query.MacroUsageQuery
import com.atlassian.plugin.ModuleDescriptor
import com.atlassian.plugin.Plugin
import com.atlassian.sal.api.component.ComponentLocator
import com.google.common.base.Function
import com.google.common.base.Predicate
import com.google.common.collect.Collections2
import com.google.common.collect.ImmutableList
import com.google.common.collect.Lists
import com.google.common.collect.Ordering

import javax.annotation.Nullable

final ConfluencePluginManager pluginManager = ComponentLocator.getComponent(ConfluencePluginManager)
PageManager pageManager = ComponentLocator.getComponent(PageManager)

String addOnKey = params[0]
Plugin plugin = pluginManager.getPlugin(addOnKey) (1)
assert plugin: "Add-on with key '${addOnKey}' not found"

List<String> pluginMacros = getMacroModuleKeys(plugin) (2)

List<SearchResult> searchResults = findAbstractPagesContainingMacro(pluginMacros) (3)

def ids = []
for (final SearchResult searchResult : searchResults) { (4)
    Page page = pageManager.getPage(searchResult.getSpaceKey(), searchResult.getDisplayTitle())
    ids << String.valueOf(page.getId())
}

return ids

private List<String> getMacroModuleKeys(Plugin plugin) {
    if (plugin == null) return Collections.emptyList()

    final Collection<ModuleDescriptor<?>> moduleDescriptors = plugin.getModuleDescriptors()

    final Collection<ModuleDescriptor<?>> macroModuleDescriptors = Collections2.filter(moduleDescriptors, new Predicate<ModuleDescriptor<?>>() {
        boolean apply(@Nullable ModuleDescriptor<?> moduleDescriptor) {
            return moduleDescriptor instanceof MacroMetadataSource
        }
    })

    final Function<ModuleDescriptor<?>, String> getKeyFunction = new Function<ModuleDescriptor<?>, String>() {
        String apply(ModuleDescriptor<?> moduleDescriptor) {
            return moduleDescriptor.getKey()
        }
    }

    final Function<ModuleDescriptor<?>, String> getNameFunction = new Function<ModuleDescriptor<?>, String>() {
        String apply(ModuleDescriptor<?> moduleDescriptor) {
            return moduleDescriptor.getName()
        }
    }

    final List<ModuleDescriptor<?>> sortedMacroModuleDescriptors = Ordering.natural().onResultOf(getNameFunction).immutableSortedCopy(macroModuleDescriptors)

    return Lists.transform(sortedMacroModuleDescriptors, getKeyFunction)
}

private List<SearchResult> findAbstractPagesContainingMacro(final List<String> pluginMacros) {

    final List<SearchResult> allResults = new ArrayList<SearchResult>()
    for (String macroName : pluginMacros) {
        doSearch(macroName, 0, SearchConstants.MAX_LIMIT, allResults)
    }

    return ImmutableList.copyOf(allResults)
}

private void doSearch(
    final String macroName, final int startIndex, final int limit, final List<SearchResult> allResults) {

    SearchManager searchManager = ComponentLocator.getComponent(SearchManager)

    final ISearch search = new ContentSearch(new MacroUsageQuery(macroName), null, null, startIndex, limit)
    final SearchResults searchResults
    try {
        searchResults = searchManager.search(search)
    } catch (InvalidSearchException e) {
        // We can't recover from this so we wrap the error in a runtime exception
        throw new RuntimeException("Error searching for pages containing the Forms for Confluence macros", e)
    }
    allResults.addAll(searchResults.getAll())
    if (searchResults.getUnfilteredResultsCount() > (startIndex + limit)) {
        doSearch(macroName, (startIndex + limit), limit, allResults)
    }
}
1 - Getting the plugin associated with the key specified
2 - Retrieving all the macros associated with the plugin
3 - Getting all the pages that are using at least one plugin macro
4 - Iterating over the pages to return the list of ids

It’s possible to use this function with the ScriptRunner macro 'CQL Search' providing the query 'content in addOnPages("com.adaptavist.confluence.formMailNG")'

The function retrieves the add-on macros defined in the plugin descriptor

For how-to questions please ask on Atlassian Answers where there is a very active community. Adaptavist staff are also likely to respond there.

Ask a question about ScriptRunner for JIRA, for for Bitbucket Server, or for Confluence.