DEV Community

Mikhail Klimenko
Mikhail Klimenko

Posted on Originally published at horizonventures.app

Confluence CQL cheat sheet: queries you can copy

#confluence #atlassian #cql #productivity

Short answer. A CQL query is field operator value, joined with AND, OR and NOT, optionally followed by ORDER BY. Pages nobody touched in six months: type = page AND lastmodified < now("-26w"). Pages with two labels at once: label = "policy" AND label = "hr", not label IN (...), which means "any of". Your starred pages: favourite = currentUser().

Where to run it

Atlassian documents CQL as part of the Confluence REST API (Advanced searching using CQL). The quickest way to try a query, with your own permissions, is to open this address in a browser where you are logged in to Confluence, with your query at the end:

https://your-site.atlassian.net/wiki/rest/api/content/search?cql=type = page AND space = DEV
Enter fullscreen mode Exit fullscreen mode

The browser fills in the encoding. You get JSON: titles, IDs and links in results, the total in totalSize. Scripts and apps use the same endpoint, which is why a query that works here works there too.

The building blocks

Field What it matches Example
type page, blogpost, comment, attachment, whiteboard, database, folder type IN (page, blogpost)
space Space key space = DEV
title Title, exact or contains title ~ "policy"
text Words anywhere in the content text ~ "vacation policy"
label Labels label = "onboarding"
created, lastmodified Dates, with functions lastmodified < now("-180d")
creator, contributor Who created or edited contributor = currentUser()
favourite, watcher, mention Starred by, watched by, mentioning a user mention = currentUser()
parent, ancestor Direct children, whole tree below a page ancestor = 123456
macro Pages using a macro macro = "drawio"
pageStatus The status lozenge on a page pageStatus = "Verified"

Operators: =, !=, <, >, <=, >= (dates), IN, NOT IN, and ~, !~ (contains, for title and text). Date functions: now("-4w"), startOfDay(), startOfWeek("-1w"), startOfMonth(), startOfYear() and their endOf pairs. User functions: currentUser().

Queries for real tasks

Stale content 

type = page AND lastmodified < now("-26w") ORDER BY lastmodified ASC
type = page AND space = DEV AND lastmodified < now("-180d")
type = page AND lastmodified < startOfYear()
type = page AND lastmodified < now("-180d") AND NOT label = "archived"
Enter fullscreen mode Exit fullscreen mode

Oldest first is the useful order: the top of the list is where the forgotten pages are. More on what to do with them in how to find the Confluence pages nobody maintains.

Labels 

label = "policy" AND label = "hr"          (both labels)
label IN ("policy", "hr")                  (either label)
type = page AND label NOT IN ("archived")
Enter fullscreen mode Exit fullscreen mode

You and your colleagues 

favourite = currentUser()                  (your starred pages)
creator = currentUser() AND type = page    (pages you created)
contributor = currentUser() ORDER BY lastmodified DESC
mention = currentUser()                    (pages that @mention you)
watcher = currentUser()
Enter fullscreen mode Exit fullscreen mode

For another person, use their account ID instead of currentUser(): names do not work in Confluence Cloud, which only matches users by account ID.

A section of the tree 

parent = 123456      (direct children only)
ancestor = 123456    (everything below, at any depth)
Enter fullscreen mode Exit fullscreen mode

The number is the page ID, the digits in the page address after /pages/.

By content 

title ~ "policy" AND type = page
text ~ "vacation policy"
macro = "drawio"                 (pages with a draw.io diagram)
type = attachment AND title ~ "docx"
pageStatus = "Rough draft"
Enter fullscreen mode Exit fullscreen mode

Recent activity 

created >= startOfWeek("-1w") AND type = blogpost
type IN (page, blogpost) AND space IN (DEV, OPS) ORDER BY created DESC
Enter fullscreen mode Exit fullscreen mode

Traps that return the wrong pages

  • m is minutes, M is months. On our test site lastmodified > now("-6m") returned nothing and now("-6M") returned every page. Use d or w if you want to avoid the question.
  • A name returns nothing, without an error. creator = "Mikhail" came back empty while creator = currentUser() found every page of the same person. In Confluence Cloud user fields take account IDs; a name is not rejected, it just matches no one.
  • label IN means any, not all. We checked on two test pages, one with labels A and B, one with only A: label IN ("A", "B") returned both pages, label = "A" AND label = "B" returned one. Label case does not matter.
  • A query cannot start with a negative clause. Atlassian's documentation says so: put NOT label = "archived" after a positive condition such as type = page AND.
  • Without parentheses, conditions are read left to right. Mixing AND and OR without brackets gives surprising results; bracket every OR group.
  • Not every field can be sorted. Ordering by a field a page can have several values of, labels for example, is not supported.
  • Brand new pages show up a little later. In our test a page created a minute earlier did not appear yet; after about a minute it did. Search runs on an index.
  • Results are capped when you ask for page bodies. Atlassian notes that with expand=body.storage.value the endpoint returns at most 50 results, whatever limit you ask for.

The arithmetic

Writing a query takes a minute once you know the fields. The cost is elsewhere: CQL results live in a JSON response or a script, not on a page your colleagues read. A monthly "pages not updated in six months" list for five teams means someone runs five queries, turns JSON into something readable and posts it, every month. That is roughly an hour a month for one person, and it stops the month that person is busy.

If you only need an answer for yourself once, the browser address above is free and enough.

When the list should live on a page

Disclosure: I build Smart Search for Confluence. It builds the CQL for you from simple settings (space, labels, words, "not updated for N days") and shows the result as a live list on a Confluence page, so readers see it without running anything. Several labels mean all of them, as most people expect. It runs inside Atlassian's infrastructure and is free for teams up to ten people.

Frequently asked

How do I find pages not updated in 6 months?

type = page AND lastmodified < now("-26w") ORDER BY lastmodified ASC. Offsets like -180d or -6M work too; lowercase -6m is six minutes.

How do I find pages with two labels at once?

One condition per label, joined with AND. label IN returns pages with any of them.

How do I list my starred pages?

favourite = currentUser(). It only ever shows your own starred pages, never someone else's.

What is the difference between ancestor and parent?

parent gives direct children, ancestor the whole tree below a page.

Can I search by a person's name?

Not in Confluence Cloud: user fields take account IDs. currentUser() covers the common case of searching for yourself.

Related: how to list Confluence pages on a page without writing CQL, finding pages nobody maintains, and what to use now that CQL Search is ending.

Top comments (0)