Operators

OQL operators live inside where clauses. A bare value means equality; operators are only required for everything else.

"where": {
  "status": "won",                          // implicit =
  "amount": { ">": 1000 },                  // explicit operator
  "owner_id": { "in": ["abc...", "def..."] }
}

Reference

Type compatibility

Not every operator works on every type. OQL enforces these rules and rejects incompatible combinations with a clear error.

array covers embedded arrays such as emails, phones, and urls on contacts. Each exposes string subfields you can filter against; see below.

* contains works on searchable long-text fields only, not on every string. The six are listed under contains below.

** Not on the per-user booleans starred and sales_cycle_closed. See is null / is not null below.

Notes per operator

Equality

Bare values imply =. The two forms below are equivalent.

// bare value
{ "status": "won" }

// explicit operator
{ "status": { "=": "won" } }

!= keeps null and missing

!= excludes records where the field equals the given value, but it keeps records where the field is null or missing — an absent value counts as “not equal” to anything. So != is not the same as “has some other value.” When you also need the field present, add a null check:

{ "company_id": { "!=": "507f..." } }   // not that company — also matches contacts with no company
{ "company_id": { "is not": null } }    // linked to some company
{ "company_id": { "is": null } }        // no linked company

between

Lower bound inclusive. The upper bound depends on what you pass:

• A literal upper bound is taken exactly and inclusively (<= the value given). On a :date field that covers the whole day; on a :time field it’s that precise instant — pass an explicit end instant if you want a full day.
• A date-function upper bound (e.g. TODAY()) uses that period’s half-open range, so it stops just before the first instant after the period. See Functions.

Keeping literal bounds exact stops sub-day ranges from over-returning, while calendar ranges stay natural.

{ "amount": { "between": [100, 500] } }                          // 100–500 inclusive
{ "close_date": { "between": ["2026-01-01", "2026-03-31"] } }    // Q1, both days included
{ "call_time": { "between": [{ "DAYS_AGO": [30] }, "TODAY()"] } } // last 30 days, through end of today

like

String-only pattern match. Use % for any-length wildcard and _ for a single character. Anchored on both ends. Case insensitive.

{ "first_name": { "like": "Al%" } }       // starts with "Al"
{ "company_name": { "like": "%Ltd" } }    // ends with "Ltd"
{ "phones.number": { "like": "+353%" } }  // Irish phone numbers

Constraints:

• Maximum 3 wildcards (% or _) per pattern.
• Maximum 100 characters per pattern.
• Empty patterns are rejected; use = "" or {"is": null} instead.

contains

Full-text search over long text. Word-based, not substring: every word of the phrase must be a whole word in the text (or its stem), and the phrase must appear in that order.

{ "text": { "contains": "renewal pricing" } }   // notes, calls, meetings, deals
{ "background": { "contains": "golf" } }        // contacts
{ "description": { "contains": "logistics" } }  // companies

Valid on six fields:

What matches:

Constraints:

• One contains per query.
• Not valid through a lookup (contact.background). Query that entity directly instead.
• Non-empty string, maximum 200 characters.
• The phrase is matched literally, so a line break or double space between two words breaks it.

contains ANDs with every other condition, and composes with order_by, group_by, having, and distinct. Results come back in the entity’s normal order; there is no relevance ranking.

{
  "from": "notes",
  "select": ["date", "text", "contact.last_name"],
  "where": {
    "text": { "contains": "pricing" },
    "author_id": "ME()",
    "date": "LAST_MONTH()"
  },
  "order_by": [{ "date": "desc" }]
}

in

Membership against a list. The list must be non-empty.

{ "status":  { "in": ["won", "pending"] } }
{ "tags":    { "in": ["VIP", "Hot"] } }   // matches if any tag overlaps

is null / is not null

Explicit null checks, separate from !=. For embedded array fields (emails, phones, urls), is null matches records with an empty or missing array; is not null matches records with at least one element.

{ "owner_id": { "is": null } }
{ "emails":   { "is not": null } }

The per-user booleans on contacts, starred and sales_cycle_closed, reject both. They are computed per caller, so they are always true or false and a null check would answer the wrong question. Filter them by value instead.

Filtering on embedded arrays

emails, phones, and urls on contacts are arrays of objects. Filter using dotted subfield syntax. The match is satisfied if any element matches.

{ "emails.address": "alice@example.com" }
{ "phones.number":  { "like": "+353%" } }
{ "urls.type":      "linkedin" }

Each entity page lists the subfields available on its array fields. Filtering by the bare field name (without subfield) only works with is null / is not null.