Query Syntax

DealCloud uses a MongoDB-like query syntax for filtering data. This page covers common patterns; queries are sent over intapp-rest-client to the rows/query endpoints.

Basic Query Format

dc.read_data(
    "Company",
    output="pandas",
    query="{FieldName: {$operator: value}}"
)

Query Operators

Comparison Operators

Operator	Description	Example
`$eq`	Equals	`{Status: {$eq: 'Active'}}`
`$not`	Not equals	`{Status: {$not: 'Closed'}}`
`$gt`	Greater than	`{Revenue: {$gt: 1000000}}`
`$gte`	Greater than or equal	`{Revenue: {$gte: 1000000}}`
`$lt`	Less than	`{Revenue: {$lt: 500000}}`
`$lte`	Less than or equal	`{Revenue: {$lte: 500000}}`
`$between`	Between two values	`{Revenue: {$between: [100000, 500000]}}`

# Exact match
data = dc.read_data(
    "Company",
    output="pandas",
    query="{Status: {$eq: 'Active'}}"
)
 
# Shorthand for equals
data = dc.read_data(
    "Company",
    output="pandas",
    query="{Status: 'Active'}"  # Implicit $eq
)

String Operators

Operator	Description	Example
`$contains`	Contains substring	`{Name: {$contains: 'Corp'}}`
`$startswith`	Starts with	`{Name: {$startswith: 'Acme'}}`
`$endswith`	Ends with	`{Name: {$endswith: 'Inc.'}}`

# Contains substring
data = dc.read_data(
    "Company",
    output="pandas",
    query="{CompanyName: {$contains: 'Technology'}}"
)

Set Operators

Operator	Description	Example
`$in`	In list	`{Industry: {$in: [101, 102, 103]}}`
`$nin`	Not in list	`{Status: {$nin: [201, 202]}}`

# Match any value in list
data = dc.read_data(
    "Company",
    output="pandas",
    query="{Industry: {$in: [101, 102, 103]}}"  # Choice IDs
)

Logical Operators

Operator	Description	Example
`$and`	All conditions	`{$and: [{A: 1}, {B: 2}]}`
`$or`	Any condition	`{$or: [{A: 1}, {B: 2}]}`

# All conditions must match
data = dc.read_data(
    "Company",
    output="pandas",
    query="{$and: [{Status: 'Active'}, {Revenue: {$gt: 1000000}}]}"
)

Filtering by Entry ID

# Single entry
data = dc.read_data(
    "Company",
    output="pandas",
    query="{EntryId: 12345}"
)
 
# Multiple entries (MongoDB-style query)
data = dc.read_data(
    "Company",
    output="pandas",
    query="{EntryId: {$in: [12345, 12346, 12347]}}"
)
 
# Multiple entries (DealCloud query string - useful for delta sync)
entry_ids = [12345, 12346, 12347]
ids_str = ",".join(map(str, entry_ids))
data = dc.read_data(
    "Company",
    output="list",
    query=f"EntryId in ({ids_str})"
)

Using Entry ID Queries for Delta Sync

When working with delta sync workflows, you often need to fetch specific entries by their IDs. The query string format is convenient for this:

from datetime import datetime, timedelta
 
# Get modified entry IDs
last_sync = datetime.now() - timedelta(hours=1)
changes = dc.get_modified_entries("Company", last_sync)
 
# Separate modifications from deletions
modified_ids = [c.entry_id for c in changes if not c.is_deleted]
 
# Fetch full data for modified entries
if modified_ids:
    ids_str = ",".join(map(str, modified_ids))
    data = dc.read_data(
        "Company",
        query=f"EntryId in ({ids_str})",
        output="list"
    )
    # Process the modified data
    for row in data:
        your_db.upsert(row)

💡

The query string format EntryId in (123, 456, 789) is equivalent to the MongoDB-style {EntryId: {$in: [123, 456, 789]}}. Use whichever format is more convenient for your use case.

Filtering by Reference Fields

Reference fields use Entry IDs:

# Filter contacts by company
data = dc.read_data(
    "Contact",
    output="pandas",
    query="{Company: {$in: [12345, 12346]}}"  # Company Entry IDs
)
 
# Exclude contacts at specific companies
data = dc.read_data(
    "Contact",
    output="pandas",
    query="{Company: {$nin: [99999]}}"
)

Filtering by Choice Fields

Choice fields use choice value IDs:

# Get choice IDs from schema
fields = dc.get_fields("Company")
industry_field = next(f for f in fields if f.apiName == "Industry")
 
for choice in industry_field.choiceValues:
    print(f"{choice.id}: {choice.name}")
# 101: Technology
# 102: Finance
# 103: Healthcare
 
# Filter by choice ID
data = dc.read_data(
    "Company",
    output="pandas",
    query="{Industry: {$in: [101, 102]}}"  # Tech and Finance
)

Filtering by User Fields

User fields use user IDs:

# Get user IDs
users = dc.get_users()
for u in users:
    print(f"{u.id}: {u.name}")
 
# Filter by user
data = dc.read_data(
    "Deal",
    output="pandas",
    query="{AssignedTo: {$in: [5678]}}"  # User ID
)

Date Filtering

⚠️

Date queries match the date portion only. For time-sensitive queries, use the History API.

# After a date
data = dc.read_data(
    "Company",
    output="pandas",
    query="{CreatedDate: {$gte: '2024-01-01'}}"
)
 
# Date range
data = dc.read_data(
    "Company",
    output="pandas",
    query="{$and: [{CreatedDate: {$gte: '2024-01-01'}}, {CreatedDate: {$lte: '2024-12-31'}}]}"
)

Null Checks

# Has value
data = dc.read_data(
    "Company",
    output="pandas",
    query="{Revenue: {$not: null}}"
)
 
# Is null (no value)
# Use client-side filtering for null checks:
data = dc.read_data("Company", output="pandas")
missing_revenue = data[data["Revenue"].isna()]

Query Best Practices

1. Use Specific Fields

# Good: Server-side filtering
data = dc.read_data(
    "Company",
    output="pandas",
    query="{Status: 'Active'}",
    fields=["CompanyName", "Revenue"]  # Only needed fields
)
 
# Avoid: Fetching everything
data = dc.read_data("Company", output="pandas")
active = data[data["Status"] == "Active"]  # Client-side filter

2. Use $in for Multiple Values

# Good: Single query with $in
data = dc.read_data(
    "Company",
    output="pandas",
    query="{Industry: {$in: [101, 102, 103, 104, 105]}}"
)
 
# Avoid: Multiple queries
for industry_id in [101, 102, 103, 104, 105]:
    data = dc.read_data("Company", output="pandas", query=f"{{Industry: {industry_id}}}")

3. Build Queries Programmatically

def build_query(status=None, min_revenue=None, industries=None):
    conditions = []
    
    if status:
        conditions.append(f"{{Status: '{status}'}}")
    if min_revenue:
        conditions.append(f"{{Revenue: {{$gt: {min_revenue}}}}}")
    if industries:
        ids = ", ".join(str(i) for i in industries)
        conditions.append(f"{{Industry: {{$in: [{ids}]}}}}")
    
    if not conditions:
        return None
    if len(conditions) == 1:
        return conditions[0]
    return f"{{$and: [{', '.join(conditions)}]}}"
 
# Usage
query = build_query(status="Active", min_revenue=1000000)
data = dc.read_data("Company", output="pandas", query=query)

Filter Operations API - Full filter documentation
Basic Reads - Read operations
Views - Pre-configured filters

Reading from Views Streaming Reads