Searching and filtering custom object records

Custom objects are built out of custom fields. When searching and filtering for data within custom object records, the type of custom field containing the data determines which operators are supported. This article describes those operators supported for each custom field type.

See Filtered Search of Custom Object Records.

About the filtered search request

The POST /api/v2/custom_objects/{custom_object_key}/records/search?query=* endpoint returns an array of custom object records that meet your filtered search criteria.

To understand how to use this endpoint, it's important to understand how we're using the following terminology.

• Filters are JSON objects and can be applied to one or more comparison objects within a logical namespace. A filter object is comprised of arrays of logical AND or logical OR comparison objects.
• A comparison object is a JSON object that consists of the custom field's key, an operator, and the value you're filtering for. A comparison object is sometimes referred to as the "attribute" you're filtering for.

Example

In the following example, each of the objects listed in the $or array is a comparison object (also called "attribute").

"filter": {
    "$or": [
      { "custom_object_fields.field_key": { "$eq": "value" } },
      { "external_id": { "$eq": "id123" } }
    ]
  }

Supported operators for filtered search by field type

Standard fields

Custom objects have the following standard fields that you can filter on.

Field type	Supported operators	Meaning (in same order)
created_at	$gt	after
created_by_user	$eq	is equal to a user id
external_id	$eq	is equal to an external id
name	$contains	contains
updated_at	$gt	after
updated_by_user	$eq	is equal to a user id

Example

The following example shows a filter for records created by a user with the id 123456789:

{ 
  "filter": {
    "created_by_user": { "$eq": "123456789" }
  }
}

Custom fields

The operators supported for each custom field type are tailored to the type of data stored within the field type.

Field type	Supported operators	Meaning (in same order)
Checkbox	$eq	is
Date	$eq, $noteq, $gt, $gte, $lt, $lte, $exists	is, is not, after, after or on, before, before or on, present, in range
Decimal	$eq, $noteq, $gt, $gte, $lt, $lte, $exists, $in, $notin	is, is not, after, after or on, before, before or on, present, in, not in
Drop-down	$eq, $noteq, $contains, $notcontains, $exists	is, is not, contains, not contained, present
Integer	$eq, $noteq, $gt, $gte, $lt, $lte, $exists, $in, $notin	is, is not, after, after or on, before, before or on, present, in, not in
Lookup Relationship	$eq, $noteq, $exists	is, is not, present
Multi-line text	$eq, $noteq, $contains, $notcontains, $exists	is, is not, contains, not contained, present
Multi-select	$eq, $noteq, $contains, $notcontains, $exists, $in, $notin	is, is not, contains, not contained, present, in, not in
Text	$eq, $noteq, $contains, $notcontains, $exists	is, is not, contains, not contained, present

Additional examples

The following examples highlight popular and more complex filtered searches.

Multiple filters example

The filtered search endpoint supports multiple filters within a single query. The following example is based on a custom object named car, which contains a custom text field with the key make, a custom integer field with the key year, and a custom checkbox field with the key available.

In this example, you're searching for car records that meet the following criteria:

• The car was made in 2015.
• The car is available.
• The make value contains either Ford or Dodge.

The filter object contains both an $and array and an $or array.

{
  "filter": {
    "$and": [
      { "custom_object_fields.year": { "$eq": 2015 } },
      { "custom_object_fields.available": { "$eq": true } }
    ],
    "$or": [
      { "custom_object_fields.make": { "$contains": "Ford" } },
      { "custom_object_fields.make": { "$contains": "Dodge" } }
    ]
  }
}

Filtering for a date range

When performing a filtered search on a custom date field, you can use a combination of the $lt or $lte and $gt or $gte operators to define your date range. Using the same car custom object from the previous example, let's say the object contains a custom date field with the key purchase_date.

In this example, you're searching for car records that were purchased on or after April 15, 2015 and before April 20, 2015.

{
  "filter": {
    "$and": [
      { "custom_object_fields.purchase_date": { "$lt": "2015-04-20" } }
      { "custom_object_fields.purchase_date": { "gte": "2015-04-15" } }
    ]
  }
}