Filtering and Sorting

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

Getting Started

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:

1. It explicitly uses a logical operator $and.
2. It explicitly states which field we want to match against (full_name and normalized_email_address.)
3. It explicitly specifies an operator ($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"
        }
    }
}

Operators

The following operators can be used in filters:

Logical

Comparison

Negation Operator

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"
            }
        }
    },
}

Advanced Filters With Paths

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!

Filtering List Entries through parent Records

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

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.

Advanced Sorts with Paths

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"
    }
  ]
}