Advanced Bucket Queries
How to write advanced queries
Advanced Queries can be used as part of a filter for a bucket or within a bucket search when you need to perform more complex logic queries or range queries over your data. For example if you wish to apply an OR logic or AND logic this can be accomplished with Advanced Queries.
An advanced query is text composed of single or multiple terms in a logical way to describe the query you wish to perform that is entered into either an Advanced Query for a bucket filter, on a bucket search.
Simple Examples
current_state:activeMatches any cases that current_state contains the word ‘active’
title:(quick brown)Matches any case that title contains either ‘quick’ or ‘brown’
author:”John Smith”Matches any case that the author field contains the exact phrase “John Smith”
_exists_:titleMatches any field where title has any non-null value
NOT _exists_:titleMatches any field where title has no value, or is missing.
Wildcards
Wildcard searches can be run on individual terms, using ? to replace a single character, and * to replace zero or more characters:
qu?ck bro*Ranges
Date Ranges
Periods of time from the current time, eg within the last 20 minutes there are some helpers to configure.
within_the_last_x
within_the_next_x
before_the_last_x
after_the_next_xwhere x is a description of time, days, weeks, months or years, eg;
2d
6w
3m
1yso a full example would be
created_at: within_the_last_2dreturns any cases that were created within the last 2 days.
Generic Ranges
Inclusive ranges are specified with square brackets [min TO max] and exclusive ranges with curly brackets {min TO max}.
All days in 2012:
date:[2012/01/01 TO 2012/12/31]Numbers 1..5
count:[1 TO 5]Tags between alpha and omega, excluding alpha and omega:
tag:{alpha TO omega}Numbers from 10 upwards
count:[10 TO *]Dates before 2012
date:{* TO 2012/01/01}Yesterday
date:[now-1d/d TO now/d]Curly and square brackets can be combined:
Numbers from 1 up to but not including 5
count:[1..5}Ranges with one side unbounded can use the following syntax:
age:>10
age:>=10
age:<10
age:<=10 Note: To combine an upper and lower bound with the simplified syntax, you would need to join two clauses with an AND operator: age:(>=10 AND < 20) age:(+>=10 +<20)
boolean operators
The preferred operators are + (this term must be present) and – (this term must not be present). All other terms are optional. For example, this query:
quick brown +fox -newsstates that:
- fox must be present
- news must not be present
- quick and brown are optional — their presence increases the relevance
The familiar operators AND, OR and NOT (also written &&, || and !) are also supported. However, the effects of these operators can be more complicated than is obvious at first glance. NOT takes precedence over AND, which takes precedence over OR. While the + and – only affect the term to the right of the operator, AND and OR can affect the terms to the left and right.
Rewriting the above query using AND, OR and NOT demonstrates the complexity:
quick OR brown AND fox AND NOT newsThis is incorrect, because brown is now a required term.
(quick OR brown) AND fox AND NOT newsThis is incorrect because at least one of quick or brown is now required and the search for those terms would be scored differently from the original query.
((quick AND fox) OR (brown AND fox) OR fox) AND NOT newsThis form now replicates the logic from the original query correctly, but is more complicated to read and debug.
Grouping
Multiple terms or clauses can be grouped together with parentheses, to form sub-queries:
(quick OR brown) AND foxReserved characters
If you need to use any of the characters which function as operators in your query itself (and not as operators), then you should escape them with a leading backslash. For instance, to search for (1+1)=2, you would need to write your query as \(1\+1\)=2.
The reserved characters are:
- – && || ! ( ) { } [ ] ^ ” ~ * ? : \ /
Failing to escape these special characters correctly could lead to a syntax error which prevents your query from running.