Filtering

A search filter is specified as a key-value pair in a JSON object, where the key is an attribute of the entity, and the value is another JSON object with a single key-value pair. The key of the inner object is an operator, and the value is the operand. Multiple key-value pairs in an object are joined conjunctively (logical AND). Multiple objects in an array are joined disjunctively (logical OR).

Operators

The list of supported operators and their supported operand types is as follows:

[
  { "category": { "equal": "Clients" } },
  { "middle_name": { "blank": true } },
  { "last_name": { "ends_with": "son" } }
]

[
  { "priority": { "greater_than": 7 } },
  { "probability": { "in_range": [50, 75] } }
]

[
  { "create_date": { "less_than": "2016-10-28T18:31:28Z" } },
  { 
    "start": { "greater_than": "2016-11-04T18:33:55Z" },
    "end": { "blank": true }
  }
]

[
  {
    "email_addresses": { 
      "any": [
        { "address": { "equal": "[email protected]" } }
      ]
    } 
  },
  { "social_profiles": { "not_blank": true } }
]

{ "companies": { "any": { "name": { "contains": "Acme" } } } }

🚧

When using one of the operators blank and not_blank, its value has no effect. Hence the following subexpressions are equivalent:

{ "city": { "blank": false }}{ "city": { "blank": true }}{ "city": { "blank": "foo" }}

🚧

One operator per attribute

Each attribute's object should contain a single operator. If it contains more than one, only the first is used and the rest are ignored. For example, { "last_name": { "starts_with": "Mc", "ends_with": "son" } } only applies starts_with. To combine conditions on one attribute, use a range operator where one exists, e.g. in_range. The exception is extra_fields, which applies every operator it is given.

Operand types

• Numbers must be sent as JSON numbers: { "priority": { "greater_than": 7 } }, not "7". A string returns 400 with invalid_operand_type.
• Lists for any, not_any and any_ignore_case are JSON arrays. in_range and not_in_range take a two-item array, [from, to].
• Nesting: a request body can be nested at most 8 levels deep.
• Case: equal, not_equal, any and not_any are case-sensitive on text. equal_ignore_case, any_ignore_case, starts_with, ends_with, contains and their negatives ignore case.

Attributes

Most attributes are supported for search querying. However, some attributes which may contain large amounts of data, such as note details, are not available for search at this time. Each search endpoint lists the attributes it supports in its request body.

Attributes which have the colour type are currently not supported.

❗️

Search is not meant to be used for fetching objects with known references; the self attribute is not supported in searching. To match a record by reference, contacts, companies, opportunities, projects, tasks, appointments, notes, groups and forms can use the type's own name with equal or not_equal, e.g. { "contact": { "equal": "/v1/contacts/1000" } } (for emails, email_message). Most types can also be matched by numeric ID, e.g. { "contact_id": { "any": [1000, 1001] } }.

Related records

Attributes that hold other records take a search filter for those records as their operand, with any. This covers minor types such as email_addresses and phone_numbers, roles such as a contact's companies, and linked records such as tasks or notes. Minor types also accept not_any, blank and not_blank. Inside the filter, use the related type's own attributes, or its name to match by reference:

{ "companies": { "any": { "name": { "contains": "Acme" } } } }
{ "tasks": { "any": { "task": { "equal": "/v1/tasks/1000" } } } }

The operand can be a single filter object or an array of them, which are joined with OR as usual.

Examples

The following are several examples that show some example search queries, along with their equivalent Daylite smart list filter, and their hypothetical equivalent SQL query.

Contacts with the first_name of Bellerophon

{ "first_name": { "equal": "Bellerophon" } }

SELECT * FROM contacts WHERE first_name = 'Bellerophon';

Contacts with the first_name of Thor, and the last_name of Odinson

{ 
  "first_name": { "equal": "Thor" },
  "last_name":  { "equal": "Odinson" }
}

SELECT * FROM contacts WHERE first_name = 'Thor' AND last_name = 'Odinson';

Contacts which are tagged with the Greek or Norse keywords

{ 
  "keywords": { 
  	"any": ["Greek", "Norse"]
  }
}

SELECT * FROM contacts 
	WHERE array_position(keywords,'Greek') IS NOT NULL
	   OR array_position(keywords,'Norse') IS NOT NULL;

Contacts with first_name or middle_name of Loki

[
	{ "first_name": { "equal": "Loki" } },
	{ "middle_name": { "equal": "Loki" } }
]

SELECT * FROM contacts WHERE first_name = 'Loki' OR middle_name = 'Loki';

Contacts with social_profiles for either Twitter or Facebook with the username MjölnirMaster

{
	"social_profiles": {
  	"any": [
      { 
        "service": { "equal": "Twitter" },
        "username": { "equal": "MjölnirMaster" }
      },
      { 
        "service": { "equal": "Facebook" },
        "username": { "equal": "MjölnirMaster" }
      }
    ]
  }
}

SELECT * FROM contacts WHERE id IN (
  SELECT contact_id FROM social_profiles 
   WHERE service = 'Twitter' AND username = 'MjölnirMaster' 
	    OR service = 'Facebook' AND username = 'MjölnirMaster'
);

📘

Minor Entities

When the type of an attribute is a minor entity, such as social_profiles or phone_numbers, the operand will be a sub-search query which has the same structure as the main search query.

Daylite smart lists do not support searching by the service attribute of a social_profile, so the closest equivalent smart list might return more contacts.

Contacts which are linked to a specific company

{
  "companies": {
     "any": { "company": { "equal": "/v1/companies/1000" } }
  }
}

SELECT * FROM contacts WHERE id IN (
  SELECT lhs.contact_id 
    FROM company_contact_roles lhs
	    JOIN companies rhs ON (lhs.company_id = rhs.id)
   WHERE rhs.id = 1000
);

Again here we have a query where there is no exact Daylite smart list equivalent, but we can construct something similar enough.