Skip to main content
Skip table of contents

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


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.