Filtering and sorting

Searching for just the records you need

Some of our endpoints allow specifying filter or sorts objects as part of the request. These properties can help reduce the size of the result set or get them back in a more appropriate order.

Filtering

Attio supports two distinct formats for filtering. There is the verbose format, which supports a wider variety of operators and conditions, and a shorthand format, which is useful for quick equality checks.

Shorthand filters

Shorthand filters generally look like this:

POST /v2/objects/companies/records/query
Content-Type: application/json

{
  "filter": {
    "name": "John Smith",
    "email_addresses": "[email protected]"
  }
}

Verbose filters

Verbose filters allow joining multiple different conditions together, or querying for different properties of the attribute value. All shorthand syntaxes can be expressed in the verbose syntax. For example, the above filter can also be written as:

POST /v2/objects/companies/records/query
Content-Type: application/json

{
  "filter": {
    "$and": [
      {
        "name": {
          "full_name": {
            "$eq": "John Smith"
          }
        }
      },
      {
        "email_addresses": {
          "email_address": {
            "$eq": "[email protected]"
          }
        }
      }
    ]
  }
}

Note that we're using a logical operator ($and) to combine these two conditions, and that we're querying specific properties on the attributes (full_name and normalized_email_address).

Each attribute type has specific properties available, and so the set of possible filters varies by attribute type, but every attribute type is filterable in some way. We have examples of filtering by each attribute type in our attribute types documentation.

Next, let's walk through some more advanced filtering, starting with the comparison operators.

Comparison operators

There are nine comparison operators in total.

$eq is by far the most common, this operator checks for equality. It is supported by every attribute type. If you're using shorthand syntax, this is usually the implied operator, but it can also be specified explicitly:

{
  "name": {
    "$eq": "Contract with Apple",
  }
}

$not_empty is also available on some attribute types, this operator allows filtering results based on whether there is any value defined at all, for example:

{
  "domains": {
    "$not_empty": true
  }
}

$incan be used to check if the record or entry has a value that is part of a set.

{
  "filter": {
    "record_id": {
      "$in": [
        "000e8881-37cc-41d2-bc22-39fe35e76e6b",
        "592dc9d8-548b-4148-813f-1259055ca83c"
      ]
    }
  }
}

There are also operators for string properties and numeric/date properties:

String comparisons

For string-like properties or attributes, there are three further operators. $contains can be used for matching parts of a string, case-insensitively:

{
  "primary_location": {
    "locality": {
      "$contains": "new york"
    }
  }
}
{
  "name": {
    "$contains": "LTD"
  }
}

$starts_with and $ends_with can match on the beginning or the end of the string, respectively:

{
  "phone_numbers": {
    "$starts_with": "+44"
  }
}
{
  "job_title": {
    "$ends_with": "of things"
  }
}

Note that these can be combined on the same property or attribute to achieve a logical AND:

{
  "phone_numbers": {
    "$starts_with": "+44",
    "$ends_with": "798"
  }
}

Numeric or date comparisons

There are four operators for comparing sortable properties like numbers or dates:

  1. Less than ($lt), finds records where the value is strictly less than (exclusive) the given value
  2. Less than or equal ($lte), finds records where the value is either less than or the same as the given value
  3. Greater than or equal ($gte), finds records where the value is either greater than or the same as the given value
  4. Greater than ($gt), finds records where the value is strictly more than (exclusive) the given value
{
  "twitter_follower_count": {
    "$gte": 1000
  }
}
{
  "twitter_follower_count": {
    "$gte": 100,
    "$lt": 200
  }
}

You can also combine multiple comparators to find values between two points:

{
  "foundation_date": {
    "$gte": "2019-01-01",
    "$lte": "2019-12-31"
  }
}

Logical operators

You can combine multiple conditions using the $and, $or and $not operators.

$and operator

$and specifies that all conditions must match. If using the shorthand syntax with multiple attributes, this operator is implied. Note that you can also use the shorthand syntax for individual conditions—see the following example.

{
  "$and": [
    { "stage": "In Progress" },
    {
      "owner": {
        "referenced_actor_type": "workspace-member",
        "referenced_actor_id": "[laurens-id]"
      }
    }
  ]
}

$or operator

$or specifies that at least one of the conditions must match. It is not an exclusive-or, if all conditions match it will still pass.

{
  "$or": [
    { "stage": "One" },
    { "stage": "Two" }
  ]
}

$not operator

Attio doesn't offer negative operators, for example there is no inverse of $eq like $neq. Instead, filters should be wrapped using the $not operator, which matches all documents which don't meet the condition.

{
  "$not": {
    "name": {
      "first_name": "John"
    }
  }
}
{
  "$not": {
    "stage": "In Progress"
  }
}

Combining logical operators

It's possible to combine logical operators to build up trees of filters. For example, you could express multiple $and conditions, where some of them are a $not:

{
  "$and": [
    {  "name": {  "$contains": "Apple" } },
    {
      "$not": {
        "domains": {
          "root_domain": "apple.com"
        }
      }
    }
  ]
}

Or you could query for "deals that are owned by Alice or Bob that are worth more than $500:

{
  "$and": [
    {
      "$or": [
        { "owner": { "referenced_actor_type": "workspace-member", "referenced_actor_id": "[alices-id]" } },
        { "owner": { "referenced_actor_type": "workspace-member", "referenced_actor_id": "[bobs-id]" } }
      ],
    },
    {
      "value": {
        "$gt": 500
      }
    }
  ]
}

Paths and parents

For record reference attributes, it's possible to use special path filtering by drilling down into the target objects. This filtering method is also supported on list entries.

For example, let's assume we have a list of people we want to hire, called "Candidates" (the api_slug is candidates). We could then write a query to find entries where the person has an @apple.com email address:

{
  "path": [
    ["candidates", "parent_record"],
    ["people", "email_addresses"]
  ],
  "constraints": {
    "email_domain": "apple.com"
  }
}

Note that we're using a special attribute called parent_record that we can use for filtering any list entry. We can even use these drill-down queries to express a more complicated query, like "entries where the candidate works at the same company as Steve Jobs":

{
  "path": [
    ["candidates", "parent_record"],
    ["people", "company"],
    ["company", "team"]
  ],
  "constraints": {
    "target_object": "people",
    "target_record_id": "[steve-jobs-record-id]",
  }
}

Sorting

Sorting allows us to get our results back in a particular order, based on attribute values. Each sort must specify a direction. If the target attribute is composed of multiple properties (like (Personal) name), field can also be specified.

We can do sorting by attribute, which is either a slug or ID. For example, we could sort People by their last name, then their email address:

{
  "sorts": [
    { "direction": "asc",  "attribute": "name", "field": "last_name" },
    { "direction": "desc", "attribute": "email_addresses" }
  ]
}

We can also sort using paths, which works similarly to filtering by path. Instead of specifying attribute, you specify a path property which resolves to the attribute of the related record(s):

{
  "sorts": [
    {
      "direction": "asc",
      "path": [
        ["people", "company"],
        ["companies", "name"]
      ]
    }
  ]
}