Filter Operations
GET {{host}}/api/rest/v4/schema/filteroperations
Returns all filter operations supported by the core Data APIs.
Refer to the table and examples below to find the appropriate operator for your filter when using the Cells API or Rows API. These filter operations allow you to perform advanced queries when retrieving data from DealCloud. The following sections provide a complete list of filter operations and example queries.
GET {{host}}/api/rest/v4/schema/filteroperations
Authorization: Bearer {{token}}
Filter Operations Table
Name | Description | Cells API | Rows API | Rows API Example |
---|---|---|---|---|
Equals | Exact value | 0 | $eq | {"FirstName": "Joe"} or {"FirstName": {"$eq": "Joe"}} |
Contains | String search | 1 | $contains | {"Deal": {"$contains": "Falcon"}} |
Greater | Number or date comparison | 2 | $gt | {"DealSize": {"$gt": 500}} |
GreaterOrEqual | Number or date comparison | 3 | $gte | {"DealSize": {"$gte": 501}} |
Less | Number or date comparison | 4 | $lt | {"Revenue": {"$lt": "300"}} |
LessOrEqual | Number or date comparison | 5 | $lte | {"Revenue": {"$lte": "305"}} |
StartsWith | String comparison | 6 | $startswith | {"Name": {"$startswith": "Joe"}} |
In | Array comparison of strings | 7 | $in | {"Deal": {"$in": ["Falcon", "Bravo", "Omega"]}} |
Between | Number or date comparison | 8 | $between | {"Valuation": {"$between": [500, 1000]}} |
NotIn | Value is not in an array | 9 | $nin | {"DealStage": {"$nin": ["Closed", "Postponed"]}} |
NotEqualTo | Value is not equal to x | 10 | $not | {"DealStage": {"$nin": ["Closed", "Negotiation"]}} |
DoesntContain | String comparison for missing value | 11 | -- | |
NoData | Value is null | 12 | -- | |
NotNoData | Value is not null | 13 | -- | |
EndsWith | String comparison | -- | $endswith | {"Name": {"$endswith": "Jr."}} |
Or | Either or comparison | -- | $or | {$or: [{Company: "Intapp"},{Company: "DealCloud"}]} |
And | And comparison | -- | $and | {"$and": [{"Target": "Q4"}, {"Category": "New"}]} |
Filter Operation Examples
Below are several examples of filter operation queries using the Rows API syntax.
Single Filter Queries
{"66562_First_Name": "tim"}
{"66562_First_Name": {"$eq": "tim"}}
{"66562_First_Name": {"$not": "tim"}}
{"66562_First_Name": {"$contains": "tim"}}
{"66562_First_Name": {"$endswith": "tim"}}
{"66562_First_Name": {"$startswith": "tim"}}
{"66562_First_Name": {"$in": "tim"}}
{"66562_First_Name": {"$in": ["tim"]}}
{"66562_First_Name": {"$in": ["tim", "john", "jack"]}}
{"66562_First_Name": {"$nin": "tim"}}
{"66565_Salary": {"$nin": 1000}}
{"66565_Salary": {"$nin": [1000]}}
{"66565_Salary": {"$nin": [1000, 2000]}}
{"66565_Salary": {"$gt": "1000"}}
{"66565_Salary": {"$gt": 1000}}
{"66565_Salary": {"$gte": "1001"}}
{"66565_Salary": {"$gte": 100.15}}
{"66565_Salary": 100.15}
{"66565_Salary": {"$lt": "500"}}
{"66565_Salary": {"$lte": "505"}}
{"66565_Salary": {"$between": [500, 1000]}}
{"66565_Salary": {"$BETWEEN": [500, 1000]}}
Two Filter Queries
{"$and": [{"66562": "Alice"}, {"66563": "Stone"}]}
{"66562": "Alice", "66563": "Stone"}
[{"66562": "Alice"}, {"66563": { $startswith: "Stone"}}]
{"$or": [{"66562": "Alice"}, {"66563": "Stone"}]}
Three Filter Single Term Queries
{"$and": [{"66562": "Alice"}, {"66563": "Stone"}, {"66564": "J"}]}
[{"66562": "Alice"}, {"66563": { $startswith: "Stone"}}, {"66564": { $endswith: "J"}}]
{"$or": [{"66562": "Alice"}, {"66563": "Stone"}, {"66564": { $endswith: "J"}}]}
Three Filter Two Terms Queries
{"$and": [{"66562": "Tim"}, {"$or": [{"66562": "Alice"}, {"66563": "Stone"}]}]}
[{"66562": "Tim"}, {"$or": [{"66562": "Alice"}, {"66563": "Stone"}]}]
{"$or": [{"66562": "Tim"}, {"$and": [{"66562": "Alice"}, {"66563": "Stone"}]}]}
{"$or": [{"66562": "Tim"}, [{"66562": "Alice"}, {"66563": "Stone"}]]}
{"$or": [{"66562": "Tim"}, {"66562": "Alice", "66563": "Stone"}]}
{"$and": [{"$or": [{"66562": "Alice"}, {"66563": "Stone"}]}, {"66562": "Tim"}]}
[{"$or": [{"66562": "Alice"}, {"66563": "Stone"}]}, {"66562": "Tim"}]
{"$or": [{"$and": [{"66562": "Alice"}, {"66563": "Stone"}]}, {"66562": "Tim"}]}
{"$OR": [{"$AND": [{"66562": { $EQ: "Alice"}}, {"66563": { $NOT: "Stone"}}]}, {"66562": { $ENDSWITH: "Tim"}}]}
{"$or": [[{"66562": "Alice"}, {"66563": "Stone"}], {"66562": "Tim"}]}
{"$or": [{"66562": "Alice", "66563": "Stone"}, {"66562": "Tim"}]}
{"$or": [{"66562": "Alice", "66563": "Stone"}, {"66562": "Tim"}]}