CQL operators

An operator in CQL is one or more symbols or words which compares the value of a field on its left with one or more values (or functions) on its right, such that only true results are retrieved by the clause. Some operators may use the NOT keyword.

EQUALS

The "=" operator is used to search for content where the value of the specified field exactly matches the specified value (cannot be used with text fields; see the CONTAINS operator instead.)

To find content where the value of a specified field exactly matches multiple values, use multiple "=" statements with the AND operator.

Examples

• Find all content that was created by the user with the accountId 99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e:

  1
  2
  creator = "99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e"
  

• Find all content that has the title "Advanced Searching"

  1
  2
  title = "\"Advanced Searching\""
  

NOT EQUALS

The "!=" operator is used to search for content where the value of the specified field does not match the specified value. It cannot be used with text fields; see the DOES NOT CONTAIN ("!~") operator instead.

Currently a negative expression cannot be the first clause in a CQL statement

Examples

• Find all content in the DEV space that was created by someone other than the user with the accountId 99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e:

  1
  2
  space = DEV and not creator = "99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e"
  

  or:

  1
  2
  space = DEV and creator != "99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e"
  

• Find all content that was created by me but doesn't mention me

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

GREATER THAN

The ">" operator is used to search for content where the value of the specified field is greater than the specified value. Cannot be used with [text] fields.

The ">" operator can only be used with fields which support range operators (e.g. date fields and numeric fields). To see a field's supported operators, check the individual field reference.

Examples

• Find all content created in the last 4 weeks

  1
  2
  created > now("-4w")
  

• Find all attachments last modified since the start of the month

  1
  2
  created > startOfMonth() and type = attachment
  

GREATER THAN EQUALS

The ">=" operator is used to search for content where the value of the specified field is greater than or equal to the specified value. Cannot be used with text fields.

The ">=" operator can only be used with fields which support range operators (e.g. date fields). To see a field's supported operators, check the individual field reference.

Examples

• Find all content created on or after 31/12/2008:

  1
  2
  created >= "2008/12/31"
  

LESS THAN

The "<" operator is used to search for content where the value of the specified field is less than the specified value. Cannot be used with text fields.

The "<" operator can only be used with fields which support range operators (e.g. date fields). To see a field's supported operators, check the individual field reference.

Examples

• Find all pages lastModified before the start of the year

  1
  2
  lastModified < startOfYear() and type = page
  

LESS THAN EQUALS

The "<=" operator is used to search for content where the value of the specified field is less than or equal to than the specified value. Cannot be used with text fields.

The "<=" operator can only be used with fields which support range operators (e.g. date fields). To see a field's supported operators, check the individual field reference.

Examples

• Find blogposts created in the since the start of the fortnight

  1
  2
  created >= startOfWeek("-1w") and type = blogpost
  

IN

The "IN" operator is used to search for content where the value of the specified field is one of multiple specified values. The values are specified as a comma-delimited list, surrounded by parentheses.

Using "IN" is equivalent to using multiple EQUALS (=) statements with the OR keyword, but is shorter and more convenient. That is, typing creator IN ("99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e", "48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01", "2223:48d-3a-XXXX-XXXX-XXXX-8d9dd0e98as7") is the same as typing creator = "99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e" OR creator = "48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01" OR creator = "2223:48d-3a-XXXX-XXXX-XXXX-8d9dd0e98as7".

Examples

Find all content that mentions any of the users with the accountIds 99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e,48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01, or 2223:48d-3a-XXXX-XXXX-XXXX-8d9dd0e98as7

1
2
``` bash
mention in ("99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e", "48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01", "2223:48d-3a-XXXX-XXXX-XXXX-8d9dd0e98as7")
```

• Find all content where the creator or contributor is either the user with the accountId 99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e or the user with the accountId 48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01

  1
  2
  creator in ("99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e", "48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01") or contributor in ("99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e", "48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01")
  

NOT IN

The "NOT IN" operator is used to search for content where the value of the specified field is not one of multiple specified values.

Using "NOT IN" is equivalent to using multiple NOT_EQUALS (!=) statements, but is shorter and more convenient. That is, typing creator NOT IN ("99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e", "48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01", "2223:48d-3a-XXXX-XXXX-XXXX-8d9dd0e98as7") is the same as typing creator != "99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e" AND creator != "48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01" AND creator != "2223:48d-3a-XXXX-XXXX-XXXX-8d9dd0e98as7".

Examples

• Find all content where the creator is someone other than the users with the accountIds 99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e,48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01, or 2223:48d-3a-XXXX-XXXX-XXXX-8d9dd0e98as7:

  1
  2
  space = DEV and creator not in ("99:27935d01-XXXX-XXXX-XXXX-a9b8d3b2ae2e", "48293:5s04-XXXX-XXXX-XXXX-d7a9b9d8c9f01", "2223:48d-3a-XXXX-XXXX-XXXX-8d9dd0e98as7")
  

CONTAINS

The "~" operator is used to search for content where the value of the specified field matches the specified value (either an exact match or a "fuzzy" match -- see examples below). The "~" operator can only be used with text fields, for example:

• title
• text

Examples

• Find content created by users with a full name (depending on profile visibility) like alana

  1
  2
  creator.fullname ~ "alana"
  

• Find all content where the title contains the word "win" (or simple derivatives of that word, such as "wins"):

  1
  2
  title ~ win
  

• Find all content where the title contains a wild-card match for the word "win":

  1
  2
  title ~ "win*"
  

• Find all content where the text contains the exact phrase "advanced search":

  1
  2
  text ~ "\"advanced search\""
  

DOES NOT CONTAIN

The "!~" operator is used to search for content where the value of the specified field is not a "fuzzy" match for the specified value. The "!~" operator can only be used with text fields, for example:

• title
• text

Examples

• Find all content where the title does not contain the word "run" (or derivatives of that word, such as "running" or "ran"):

  1
  2
  space = DEV and title !~ run