API calls for searching and filtering

API endpoints are available for certain objects that allow searching and filtering. The body of these endpoints takes a single key-value pair {“filter_expression“: “<expression>”} that should conform to the filter-query language specified below.

The following objects support searching and filtering:

Comparison operators

Operator	Description	Example
CONTAINS	Substring or membership testing for string and list attributes respectively.	field3 CONTAINS 'foobar', field4 CONTAINS TRUE
IN	Tests if field is a member of a list literal. List can contain a maximum of 100 values.	field2 IN ['Goku', 'Vegeta']
GE	Tests if a field is greater than or equal to a literal value	field1 GE 1.2e-2
GT	Tests if a field is greater than a literal value	field1 GT 1.2e-2
LE	Tests if a field is less than or equal to a literal value	field1 LE 9000
LT	Tests if a field is less than a literal value	field1 LT 9.02
NE	Tests if a field is not equal to a literal value	field1 NE 42
EQ	Tests if a field is equal to a literal value	field1 EQ 42

Search operator

The SEARCH operator filters for items that have any filterable attribute that contains the input string as a substring. The comparison is done case-insensitively. This is not restricted to attributes with string values. Specifically `SEARCH '12'` would match an item with an attribute with an integer value of `123`.

Logical operators

Logical operators are ordered by precedence.

Operator	Description	Example
NOT	Logical NOT (right associative)	NOT filed1 LE 9000
AND	Logical AND (left associative)	field1 GT 9000 AND field2 EQ 'Goku'
OR	Logical OR (left associative)	field1 GT 9000 OR field2 EQ 'Goku'

Grouping

Parentheses '()' can be used to override operator precedence.

For example:

NOT (field1 LT 1234 AND field2 CONTAINS 'foo')

Literal Values

Literal	Description	Example
Nil	Represents the absence of a value	nil, Nil, nIl, NIL
Boolean	true/false boolean	true, false, True, False, TRUE, FALSE
Number	Signed integer and floating point numbers. Also supports scientific notation.	0, 1, -1, 1.2, 0.35, 1.2e-2, -1.2e+2
String	Single or double quoted	"foo", "bar", "foo bar", 'foo', 'bar', 'foo bar'
Datetime	Formatted according to RFC3339	2018-04-27T18:39:26.397237+00:00
List	Comma-separated literals wrapped in square brackets	[0], [0, 1], ['foo', "bar"]

Limitations

Not all fields of the objects are filterable or searchable. The allowed fields for the specific object are listed in the body description of that object API endpoint.
For a filterable field of the form-field1[field2], the query within filter_expression should start with the "contains" operator.
- For example, for a filterable-field of "domain[domain_name]" the query body should be {“filter_expression": "domain contains {domain_name EQ ‘Account_NO’}”}
A maximum of 8 unique identifiers may be used inside a filter expression.

Filtering usage examples

Below is a sample 'fruit_inventory' table containing information that will be filtered using the above syntax.

id	name	color	size	quantity	in_season
1	apple	red	medium	4	false
2	watermelon	red	large	1	true
3	strawberry	red	small	10	true
4	orange	orange	medium	7	false
5	kiwi	green	small	3	false
6	raspberry	red	small	20	false
7	lemon	yellow	medium	2	true
8	lime	green	small	8	false
9	pineapple	yellow	large	3	true
10	blueberry	blue	small	132	true

Example 1:

This example uses the CONTAINS operator to search for the substring "berry" in the name field.

CODE

{"filter_expression": "name CONTAINS 'berry'"}

This query returns the following objects:

id	name	color	size	quantity	in_season
3	strawberry	red	small	10	true
6	raspberry	red	small	20	false
10	blueberry	blue	small	132	true

Example 2:

This example uses the GT operator to filter for quantities greater than 5 and the EQ operator to filter for size "small".

CODE

{"filter_expression": "quantity GT 5 AND size EQ 'small'"}

This query returns the following objects:

id	name	color	size	quantity	in_season
3	strawberry	red	small	10	true
6	raspberry	red	small	20	false
8	lime	green	small	8	false
10	blueberry	blue	small	132	true

Example 3:

This example uses the NOT operator to filter for objects where the color is not in list ['red', 'orange', 'green'].

CODE

{"filter_expression": "NOT color IN ['red','orange','green']"}

This query returns the following objects:

id	name	color	size	quantity	in_season
7	lemon	yellow	medium	2	true
9	pineapple	yellow	large	3	true
10	blueberry	blue	small	132	true

Example 4:

This example uses the EQ operator to filter for objects where the boolean value of in_season is equal to true.

CODE

{"filter_expression": "in_season EQ true"}

This query returns the following objects:

id	name	color	size	quantity	in_season
2	watermelon	red	large	1	true
3	strawberry	red	small	10	true
7	lemon	yellow	medium	2	true
9	pineapple	yellow	large	3	true
10	blueberry	blue	small	132	true

Example 5:

This example uses a combination of many operators with grouping to filter for objects that are green, small, and have a quantity greater than or equal to 8, or objects that are medium, not in season, and are in list ['apple', 'lemon'].

CODE

{"filter_expression": "(color EQ ‘green’ AND size EQ ‘small’ AND quantity GE 8) OR (size EQ ‘medium’ AND in_season EQ false AND name IN [‘apple’, ‘lemon’])"}

This query returns the following objects:

id	name	color	size	quantity	in_season
1	apple	red	medium	4	false
8	lime	green	small	8	false

Example 6:

This example demonstrates the filter query on an 'order' table (described below), which is joined to the 'fruit_inventory' table (above) by the fruit-'name' column. Use the 'contains' query only when the allowed filterable_attribute in the API mentions

explicitly like 'order[order_id]' instead of 'order_id'.

order_id	name	order_quantity
1	apple	2
2	strawberry	5
3	lime	7

CODE

{"filter_expression": "order contains {name EQ 'lime'}"}

This query returns the following objects:

order_id	name	order_quantity
3	lime	7