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

🚧

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

Attributes

Most attributes are supported for search querying. However, attributes which may contain large amounts of data, such as note details are not available for search at this time.

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.

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, unless also tagged with Comics

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

SELECT * FROM contacts 
	WHERE array_position(keywords,'Greek') IS NOT NULL
    AND array_position(keywords,'Norse') IS NOT NULL
    AND array_position(keywords,"Comics") IS 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 '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.