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:
executions
Comparison operators
Operator | Description | Example |
---|---|---|
CONTAINS | Substring or membership testing for string and list attributes respectively. | field3 CONTAINS 'foobar', |
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.
{"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".
{"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'].
{"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.
{"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'].
{"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 |
{"filter_expression": "order contains {name EQ 'lime'}"}
This query returns the following objects:
order_id | name | order_quantity |
---|---|---|
3 | lime | 7 |