Some endpoints, such as List Records, can include a filter
or sorts
object in their request payload.
A simple example:
{ "filter": { "name": { "full_name": "John Smith" } }, "sorts": [ { "direction": "asc", "attribute": "name", "field": "last_name" } ] }
Filters can be written in shorthand, or a more flexible verbose format. The simplest shorthand looks like this:
{ "name": "John Smith", "email_addresses": "[email protected]" }
This queries for Records where the name
Attribute's default field (full_name
) exactly equals John Smith
, AND
where the email_addresses
Attribute's default field (normalized_email_address
) exactly equals [email protected]
.
We've used an implicit $and
logical operator, and default field names. We could write the same filter more explicitly, using the optionally more verbose syntax:
{ "$and": [ { "name": { "full_name": { "$eq": "John Smith" } } }, { "email_addresses": { "normalized_email_address": { "$eq": "[email protected]" } } } ] }
This differs from the shorthand example in a few ways:
$and
.full_name
and normalized_email_address
.)$eq
.)Mixing and matching shorthand is possible, e.g. omitting fields for Attributes that have default fields, or omitting operators ($eq
is default.)
It's also possible to use Attribute IDs instead of the human readable slug. The example below uses an Attribute ID for a name Attribute rather than its human readable slug (name
):
{ "c08ded8d-cd6c-4052-8cef-2a0f081a44bf": { "full_name": { "$contains": "John" } } }
The following operators can be used in filters:
Name | Description |
---|---|
$and | Joins filter clauses with a logical Returns all Records that match the conditions of both clauses. |
$or | Joins filter clauses with a logical Returns all Records that match the conditions of both clauses. |
Name | Description |
---|---|
$eq | Matches values that are equal to the specified value. |
$contains | Matches values that contain the specified string value. |
$gt | Matches values that are greater than the specified number value. |
$gte | Matches values that are greater than or equal to the specified number value. |
$lt | Matches values that are less than the specified number value. |
$lte | Matches values that are less than or equal to the specified number value. |
$is_empty | Matches when a record has no value for the given Attribute field. |
$starts_with | Matches values that start with the specified string value. |
$ends_with | Matches values that end with the specified string value. |
There is no $neq
operator to match against values that do not equal the specified value. Instead, we can wrap a filter predicate with a $not
operator, to match all documents that do not meet the condition.
For example, the following will match all Records that do not have a name
Attribute equal to John Smith
:
{ "$not": { "name": "John Smith" }, }
Using more verbose syntax, to specify field name and operator, also works with the $not
operator:
{ "$not": { "name": { "first_name": { "$eq": "John" } } }, }
Objects can have a special type of Attribute that can reference a Record. When creating a new Attribute in the web UI, you'll notice that you can choose a Record
Attribute Type option.
Say that we created a Record
type Attribute called Manager
for our People Object. For some People in our data, we add a Manager
value referencing another Person Record to express who that person's manager is.
We can use a path
in our filters to add constraints that 'drill' through relationships (called a drilldown
). We can write filter queries that answer questions like "Who are all the People with a manager whose full name is John Smith?"
Here is an example of a filter that answers that question, using path
syntax:
{ "path": [ ["people", "manager"], ["people", "name"] ], "constraints": { "full_name": { "$eq": "John Smith" } } }
Notice that we have a path
array, consisting of pairs of Objects and Attributes ["someObject", "someAttribute"]
. The manager
Attribute is a Record type Attribute, so it references other Records. It will resolve its value to the referenced manager's Person Record. The second part of the path says "Take that resolved Person Record, and use its name
Attribute with the filter contraint."
The path resolved to a name
Attribute, and the filter constraints
say to filter on the full_name
field of this name
Attribute, where the value exactly equals "John Smith".
The example path
consists of human readable slugs like the Attribute slug manager
, but can also use Object IDs and Attribute IDs instead, like ["f2f2a9e2-4cd6-4b36-9e8d-674742564a1c", "a28ee250-a1ed-4081-96ef-434d37062b7f"]
.
We can do powerful, high performance filter queries 'drilling' through relationships using paths
. For instance we could go further and filter against people whose manager's manager meets some criteria!
You might want to answer a question like "Get me all List Entries whose parent Record has email address [email protected]"
All List Entries have a Parent Record for example the Person or Company that you added to the List. Some Attributes are on the Record, and some Attributes are on the List Entry itself.
In the above example, John's email address is on his Person Object's email_addresses
Attribute, not on one of the List Entry's Attributes. We need to query through the List Entry's Parent Record.
We can use Paths
to achieve this. Below, we have a List with slug list_of_candidates
, populated with List Entries whose parent is a Person record. We write a query to fetch all List Entries in our Candidates List whose parent is the Person Record with email [email protected].
{ "path": [ ["list_of_candidates", "parent_record"], ["people", "email_addresses"] ], "constraints": { "email_address": "[email protected]" } }
We use paths to query through special Attribute called parent_record
. All List Entries will have a parent_record
attribute, that allows you to "drill down" through the associated parent Record in your List Entry queries.
Sorts allow us to sort the result set. We can include multiple sorts, or none at all.
Sorts specify a direction
: asc
or desc
, and an attribute
, which can be either a human readable slug like name
or an Attribute ID like c08ded8d-cd6c-4052-8cef-2a0f081a44bf
.
For example:
{ "sorts": [ { "direction": "asc", "attribute": "name", "field": "last_name" }, { "direction": "desc", "attribute": "email_addresses" }, { "direction": "desc", "attribute": "534abc37-622f-4381-8c6d-99cfe7ae744b", "field": "country" } ] }
Note that we've included 3 sorts. The second sort omits a field name, it will use the default field for the email_addresses Attribute (normalized_email_address
.) The final sort uses an Attribute ID instead of the human readable slug (location
), but does specify the country
field.
We can use paths
with sorts, just like we did with filters. This allows us to sort results based on the Attributes of Records that they have a relationship with.
Below we express the sort rule: "Sort People results by their manager's full name in descending order."
{ "sorts": [ { "direction": "desc", "path": [ ["people", "manager"], ["people", "name"] ], "field": "full_name" } ] }