CQL functions

The instructions on this page describe how to use functions in CQL to define structured search queries to search for content in Confluence.  

Functions Reference

A function in CQL appears as a word followed by parentheses which may contain one or more explicit values. In a clause, a function is preceded by an operator, which in turn is preceded by a field. A function performs a calculation on either specific Confluence data or the function's content in parentheses, such that only true results are retrieved by the function and then again by the clause in which the function is used.

This document also covers the reserved characters and words that Confluence uses.

currentUser()

Perform searches based on the currently logged-in user.

Note that this function can only be used by logged-in users. Anonymous users cannot use this function.

Syntax

1
currentUser()

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

no

no

no

no

no

no

Examples

  • Find content that was created by me

    1
    creator = currentUser()
  • Find content that mentions me but wasn't created by me

    1
    mention = currentUser() and creator != currentUser()

endOfDay()

Perform searches based on the end of the current day. See also endOfWeek(), endOfMonth, endOfYear(), startOfDay(), startOfWeek(), startOfMonth() and startOfYear().

Syntax

1
endOfDay()

or

1
endOfDay("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m)

  • If the plus/minus (+/-) sign is omitted, plus is assumed.
  • nn: number; y: year, M: month; w: week; d: day; h: hour; m: minute.

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

yes

yes

yes

yes

no

no

Examples

  • Find content created since the end of yesterday

    1
    created > endOfDay("-1d")

endOfMonth()

Perform searches based on the end of the current month. See also endOfDay, endOfWeek and endOfYear; and startOfDay, startOfWeek, startOfMonth, and startOfYear.

Syntax

1
endOfMonth()

or

1
endOfMonth("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m)

  • If the plus/minus (+/-) sign is omitted, plus is assumed.
  • nn: number; y: year, M: month; w: week; d: day; h: hour; m: minute.

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

yes

yes

yes

yes

no

no

Examples

  • Find content modified before the end of the month

    1
    lastmodified < endOfMonth()

endOfWeek()

Perform searches based on the end of the current week. See also endOfDay, endOfMonth and endOfYear; and startOfDay, startOfWeek, startOfMonth, and startOfYear.

Syntax

1
endOfWeek()

or

1
endOfWeek("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m)

  • If the plus/minus (+/-) sign is omitted, plus is assumed.
  • nn: number; y: year, M: month; w: week; d: day; h: hour; m: minute.

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

yes

yes

yes

yes

no

no

Examples

  • Find content created after the end of last week

    1
    created > endOfWeek("-1w")

endOfYear()

Perform searches based on the end of the current year. See also startOfDay, startOfWeek and startOfMonth; and endOfDay, endOfWeek, endOfMonth and endOfYear.

1
endOfYear()

or

1
endOfYear("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m)

  • If the plus/minus (+/-) sign is omitted, plus is assumed.
  • nn: number; y: year, M: month; w: week; d: day; h: hour; m: minute.

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

yes

yes

yes

yes

no

no

Examples

  • Find content created by the end of this year:

    1
    created < endOfYear()

startOfDay()

Perform searches based on the start of the current day. See also endOfWeek, endOfMonth and endOfYear; and startOfDay, startOfWeek, startOfMonth, and startOfYear.

Syntax

1
startOfDay()

or

1
startOfDay("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m)

  • If the plus/minus (+/-) sign is omitted, plus is assumed.
  • nn: number; y: year, M: month; w: week; d: day; h: hour; m: minute.

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

yes

yes

yes

yes

no

no

Examples

  • Find content created since the start of today

    1
    created > startOfDay()
  • Find content created in the last 7 days

    1
    created > startOfDay("-7d")

startOfMonth()

Perform searches based on the start of the current month. See also endOfDay, endOfWeek and endOfYear; and startOfDay, startOfWeek, startOfMonth, and startOfYear.

Syntax

1
startOfMonth()

or

1
startOfMonth("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m)

  • If the plus/minus (+/-) sign is omitted, plus is assumed.
  • nn: number; y: year, M: month; w: week; d: day; h: hour; m: minute.

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

yes

yes

yes

yes

no

no

Examples

  • Find content created since the start of the month

    1
    created >= startOfMonth()

startOfWeek()

Perform searches based on the start of the current week. See also endOfDay, endOfMonth, and endOfYear; and startOfDay, startOfWeek, startOfMonth, and startOfYear.

Syntax

1
startOfWeek()

or

1
startOfWeek("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m)

  • If the plus/minus (+/-) sign is omitted, plus is assumed.
  • nn: number; y: year, M: month; w: week; d: day; h: hour; m: minute.

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

yes

yes

yes

yes

no

no

Examples

  • Find content created since the start of the week

    1
    created >= startOfWeek()

startOfYear()

Perform searches based on the start of the current year. See also startOfDay, startOfWeek, and startOfMonth; and endOfDay, endOfWeek, endOfMonth, and endOfYear.

1
startOfYear()

or

1
startOfYear("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m)

  • If the plus/minus (+/-) sign is omitted, plus is assumed.
  • nn: number; y: year, M: month; w: week; d: day; h: hour; m: minute.

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

yes

yes

no

no

yes

yes

yes

yes

no

no

Examples

  • Find content created this year

    1
    created >= startOfYear()

favouriteSpaces()

Returns a list of space keys, corresponding to the favorite spaces of the logged-in user. 

Syntax

1
favouriteSpaces()

Supported fields

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

no

no

no

no

no

no

no

no

yes

yes

Examples

  • Find content which exists in the favorite spaces of the logged-in user 
1
space IN favouriteSpaces()
  • Find content which exists in the favorite spaces of the logged-in user as well as other listed spaces
1
space IN (favouriteSpaces(), 'FS', 'TS')

Available from version

5.9

recentlyViewedContent()

Returns a list of IDs of recently viewed content for the logged-in user.

Syntax

1
recentlyViewedContent(limit, offset)

Supported fields

  • ancestor
  • content
  • id
  • parent

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

no

no

no

no

no

no

no

no

yes

yes

Examples

  • Find contents with limit to recent 10 history
1
id in recentlyViewedContent(10)
  • Find contents with limit to recent 10 history, but skip first 20
1
id in recentlyViewedContent(10, 20)

Available from version

5.9

recentlyViewedSpaces()

Returns a list of key of spaces recently viewed by the logged-in user.

Syntax

1
recentlyViewedSpaces(limit)

Supported fields

  • space

Supported operators

=

!=

~

!~

>

>=

<

<=

IN

NOT IN

no

no

no

no

no

no

no

no

yes

yes

Examples

  • Find spaces with limit to recent 10 history
1
 space in recentlyViewedSpaces(10)

Available from version

5.9

Reserved characters

CQL has a list of reserved characters :

  • space (" ")
  • "+"
  • "."
  • ","
  • ";"
  • "?"
  • "|"
  • "*"
  • "/"
  • "%"
  • "^"
  • "$"
  • "#"
  • "@"
  • "["
  • "]"

If you wish to use these characters in queries, you need to:

  • surround them with quote-marks (you can use either single quote-marks (') or double quote-marks ("); and, if you are searching a text field and the character is on the list of reserved characters for text searches
  • precede them with two backslashes.

Reserved words

CQL has a list of reserved words. These words need to be surrounded by quote-marks if you wish to use them in queries:

"after", "and", "as", "avg", "before", "begin", "by","commit", "contains", "count", "distinct", "else", "empty", "end", "explain", "from", "having", "if", "in", "inner", "insert", "into", "is", "isnull", "left", "like", "limit", "max", "min", "not", "null", "or", "order", "outer", "right", "select", "sum", "then", "was", "where", "update"