CQL Guide

You can use Confluence Query Language (CQL) to perform advanced searches on CQL Script Jobs. Advanced searches allow you to use structured queries to search for content in Confluence Cloud.

A CQL query is similar to a JQL query in Jira. 

You can use CQL Script Jobs to run a CQL query on a specified schedule. Each time it is run, the query returns a number of pages, and then the job is performed on those returned pages based on the code written. For instance, a CQL script job could change the returned pages by adding comments or deleting attachments, but you could also use the results to fetch a list of child pages.

Components of CQL

Now that you've seen CQL in use in ScriptRunner for Confluence Cloud, let's learn about what exactly a CQL query is. To perform a basic CQL query, you'll need the three parts: 

  • Field: Represents an indexed property of content in Confluence Cloud.

  • Operator: One or more symbols or words which compares the value of the field (on its left) with one or more functions (on its right). 

  • Function: The value you want to search for.

    • If it is a system-defined value like RecentlyViewedSpace, it does not need quotation marks.

    • If it is a user-defined value, like the name of a space or label, you need quotation marks (eg. "demonstration space").

    • If it is an Atlassian reserved character or word, it needs quotation marks (eg. "$", "before", "having"). 

    • You can add () onto the end of a function to determine specifics about the function, like endOfDay("-1d").

CQL queries return all types of Confluence Cloud content including pages, blog posts, comments, and attachments.

Types of CQL queries

Basic CQL query

Putting those three components together, you would get a formula like field = function/value.

To search for a specific label in your Confluence Cloud instances, use the field label followed by the operator and the value you want to search for. If you want to search for the label test, use label = "test". Remember to use quotation marks since it's a user-defined value. 

Other examples of basic CQL queries: 

  • space = "DS": Return all pages, blog posts, comments, or attachments contained within the DS space

  • siteSearch ~ "start a discussion": Return all pages that contain the phrase "start a discussion"

  • creator = currentUser(): Return all content created by the currently logged in user

  • created > endOfDay("-1d"): Find content created since the end of yesterday
  • mention in (vburton,ssaban,ewong): Find all content that mentions either vburton or ssaban or ewong

Combined CQL query

You can add CQL keywords to combine CQL queries, so you would get a formula like field = "value" AND field = "value". This allows you to to search for more complex things.

These are example steps for using a combined CQL query: 

  1. Use the previous example of label = "test" 

  2. Add an operator of AND to search for a second statement to search for a label of users, label = "users".

Now, you have label = "test" AND label = "users", which will return any content that has both of the users and test labels. 

Other examples of combined CQL queries: 

  • space = "DS" AND type = page: Return all pages within the DS space
  • space = KEY AND type = page AND creator = username: Return all pages in a particular space that were created by a specific user

Multiple CQL queries 

You can add CQL keywords and order of operations to combine CQL queries, so you would get a formula like (field = "value" AND field = "value") OR (field = "value").

These are the example steps for using multiple CQL queries: 

  1. Use the previous example of label = "test" AND label = "users"

  2. Add a statement for the title "Development Work" without needing that space to have the labels of "test" and "users."

Now, you have (label = "test" AND label = "users") OR (title = "Development Work"), which returns any content that has both of the users and test labels or the title is Development Work.

Other examples of multiple CQL queries: 

  • (label = "internal" OR label = "secret") OR (space = "internal"): This returns content with the internal and secret labels or content in the Internal space.
  • (lastModified < startOfYear() AND creator = vburton) OR (lastModified < startOfYear() AND contributor = vburton): This returns content last modified before the start of the current year created by the vburton user or content last modified before the start of the current year and the user vburton contributed to it.  

Atlassian CQL resources

Information from the following Atlassian materials were used to help create this page. To find out more information about CQL, visit one of the following resources:

  • Advanced Searching Using CQL: "The instructions on this page describe how to define and execute a search using the advanced search capabilities of the Confluence Cloud REST API." 

  • CQL Field References: This resource contains a list of supported fields (the first part of a basic CQL query). It also lists supported operators and functions for each field, along with examples. 

  • CQL Function References: This resource contains a list of supported functions (the final part of a basic CQL query). It also lists supported operators and fields for each function, along with examples.

  • CQL Keywords References: This resource explains what keywords (AND, OR, NOT, ORDER BY) are and how to use them in CQL, along with examples. 

  • CQL Operators Reference: This resource contains a list of supported operators (the middle part of a basic CQL query), along with examples. 

  • Performing Text Searches Using CQL: "This page provides information on how to perform text searches. It applies to advanced searches when used with the CONTAINS operator."

On this page