Filter Operations
This API endpoint returns all filter operations supported by the core Data APIs.
See the table and examples below to identify the correct operator for a given filter when using the Cells API or Rows API. These filter operations enable advanced querying and filtering when pulling data from DealCloud. Below is a comprehensive list of examples demonstrating filter operation usage and syntax.
Swagger
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"}]}