Query Command¶

The xaffinity query command provides a structured JSON query language for complex data retrieval, filtering, includes, and aggregations.

When to Use Query vs Individual Commands¶

Use query when you need: - Complex filtering with multiple AND/OR/NOT conditions - Related entity data (include companies with persons) - Aggregations (count, sum, avg, groupBy) - Multi-field sorting - Analysis across large datasets

Use individual commands for: - Simple lookups (person get 123) - Basic searches (company ls --query "Acme") - Quick exports (list export Pipeline)

Basic Usage¶

# From file
xaffinity query --file query.json

# Inline JSON
xaffinity query --query '{"from": "persons", "limit": 10}'

# From stdin (piped)
echo '{"from": "persons"}' | xaffinity query

# Dry-run to preview execution plan
xaffinity query --file query.json --dry-run

Query Structure¶

A minimal query requires only the from field:

{
  "from": "persons"
}

A complete query can include:

{
  "$version": "1.0",
  "from": "persons",
  "where": { "path": "email", "op": "contains", "value": "@acme.com" },
  "include": ["companies"],
  "select": ["id", "firstName", "lastName", "email"],
  "orderBy": [{ "field": "lastName", "direction": "asc" }],
  "limit": 100
}

Supported Entity Types¶

Entity	Description	Query Type
`persons`	People in your CRM	Direct query
`companies`	Companies/organizations	Direct query
`opportunities`	Deals/opportunities	Direct query
`lists`	Affinity list definitions	Direct query
`listEntries`	Entries in Affinity lists	Requires `listId` or `listName` filter
`interactions`	Emails, calls, meetings	Include only (cannot query directly)
`notes`	Notes on entities	Include only (cannot query directly)

Entity Query Limitations¶

listEntries requires a listId or listName filter to specify which list to query:

{
  "from": "listEntries",
  "where": { "path": "listId", "op": "eq", "value": 12345 },
  "limit": 100
}

Or using list name (resolved automatically):

{
  "from": "listEntries",
  "where": { "path": "listName", "op": "eq", "value": "My Pipeline" },
  "limit": 100
}

Field name resolution: When filtering on fields.*, you can use human-readable field names instead of field IDs:

{
  "from": "listEntries",
  "where": {
    "and": [
      { "path": "listName", "op": "eq", "value": "My Pipeline" },
      { "path": "fields.Status", "op": "eq", "value": "Active" }
    ]
  }
}

Field names are resolved case-insensitively. If a field name is not found, it passes through unchanged (allowing numeric field IDs like fields.12345 to work).

interactions and notes cannot be queried directly. Instead, include them on a parent entity:

{
  "from": "persons",
  "include": ["interactions", "notes"],
  "limit": 50
}

Attempting to query interactions or notes directly will return an error:

QueryParseError: 'interactions' cannot be queried directly.
Use it as an 'include' on a parent entity instead.
Example: {"from": "persons", "include": ["interactions"]}

Filtering with WHERE¶

Simple Conditions¶

{
  "from": "persons",
  "where": { "path": "email", "op": "contains", "value": "@gmail.com" }
}

Supported Operators¶

Operator	Description	Example Value
`eq`	Equals	`"Active"`
`neq`	Not equals	`"Closed"`
`gt`	Greater than	`10000`
`gte`	Greater than or equal	`10000`
`lt`	Less than	`5000`
`lte`	Less than or equal	`5000`
`contains`	Contains substring	`"@gmail"`
`starts_with`	Starts with	`"Acme"`
`in`	Value in list	`["New", "Active"]`
`between`	Value in range	`[1000, 5000]`
`is_null`	Field is null	(no value needed)
`is_not_null`	Field is not null	(no value needed)
`contains_any`	String contains any substring (case-insensitive)	`["vip", "hot"]`
`contains_all`	String contains all substrings (case-insensitive)	`["verified", "active"]`
`has_any`	Array contains any of the values	`["LB", "MA"]`
`has_all`	Array contains all of the values	`["LB", "MA"]`

Multi-Select Field Filtering¶

Multi-select dropdown fields (like "Team Member") return arrays from the API. The query engine handles these automatically:

{
  "from": "listEntries",
  "where": {
    "and": [
      { "path": "listName", "op": "eq", "value": "Dealflow" },
      { "path": "fields.Team Member", "op": "eq", "value": "LB" }
    ]
  }
}

Operator behavior with array fields:

Operator	Single-value field	Multi-select field
`eq`	Exact match	Scalar: membership check / List: set equality
`neq`	Not equal	Scalar: not in array / List: set inequality
`in`	Value in list	Any intersection between arrays
`has_any`	Returns false	Any specified value present
`has_all`	Returns false	All specified values present

Examples:

// Find entries where Team Member includes "LB"
{ "path": "fields.Team Member", "op": "eq", "value": "LB" }

// Find entries where Team Member is exactly ["LB", "MA"] (order-insensitive)
{ "path": "fields.Team Member", "op": "eq", "value": ["LB", "MA"] }

// Find entries where Team Member includes any of ["LB", "DW"]
{ "path": "fields.Team Member", "op": "has_any", "value": ["LB", "DW"] }

// Find entries where Team Member includes both "LB" and "MA"
{ "path": "fields.Team Member", "op": "has_all", "value": ["LB", "MA"] }

Note: Array operators are case-sensitive because dropdown values from the Affinity API are exact matches.

Compound Conditions¶

AND:

{
  "from": "persons",
  "where": {
    "and": [
      { "path": "email", "op": "is_not_null" },
      { "path": "firstName", "op": "starts_with", "value": "J" }
    ]
  }
}

OR:

{
  "from": "persons",
  "where": {
    "or": [
      { "path": "email", "op": "contains", "value": "@acme.com" },
      { "path": "email", "op": "contains", "value": "@acme.io" }
    ]
  }
}

NOT:

{
  "from": "persons",
  "where": {
    "not": { "path": "status", "op": "eq", "value": "Inactive" }
  }
}

Field Paths¶

Access nested fields with dot notation:

{
  "from": "listEntries",
  "where": { "path": "fields.Status", "op": "eq", "value": "Active" }
}

Array access:

{
  "from": "persons",
  "where": { "path": "emails[0]", "op": "contains", "value": "@" }
}

Date Filtering¶

Relative dates:

{
  "from": "listEntries",
  "where": {
    "and": [
      { "path": "listName", "op": "eq", "value": "Pipeline" },
      { "path": "createdAt", "op": "gte", "value": "-30d" }
    ]
  }
}

Format	Meaning
`-30d`	30 days ago
`+7d`	7 days from now
`today`	Start of today
`now`	Current time
`yesterday`	Start of yesterday
`tomorrow`	Start of tomorrow

Fetch related entities in a single query:

{
  "from": "persons",
  "include": ["companies", "opportunities"],
  "limit": 50
}

Available Relationships¶

From	Can Include
`persons`	`companies`, `opportunities`, `interactions`, `notes`
`companies`	`persons`, `opportunities`, `interactions`, `notes`
`opportunities`	`persons`, `companies`, `interactions`, `notes`
`listEntries`	`persons`, `companies`, `opportunities`, `interactions`

Note: For listEntries, the include dynamically resolves based on the entry's entity type (person, company, or opportunity). For example, company entries can include persons (associated persons) and interactions (company interactions).

Note: Includes fetch relationship IDs in parallel (N calls), then batch-fetch full records via V2 API. Use --dry-run to preview the cost.

Parameterized Includes¶

For listEntries, you can customize includes with parameters:

{
  "from": "listEntries",
  "where": { "path": "listName", "op": "eq", "value": "Dealflow" },
  "include": [
    { "interactions": { "limit": 50, "days": 180 } },
    { "opportunities": { "list": "Pipeline" } },
    { "persons": { "where": { "path": "firstName", "op": "contains", "value": "John" } } }
  ]
}

Parameter	Description	Applicable To
`limit`	Maximum records to fetch	`interactions`
`days`	Lookback period in days	`interactions`
`list`	Scope to specific opportunity list	`opportunities`
`where`	Filter included entities	`persons`, `companies`, `opportunities`

In table output, included data appears as separate tables (e.g., "Included: companies"). In JSON output, included data appears in a separate included section with deduplicated records.

Expanding Computed Data¶

Use expand to add computed data directly to each record (unlike include which fetches separate entities).

Interaction Dates¶

Add interaction date summaries to records:

{
  "from": "companies",
  "expand": ["interactionDates"],
  "limit": 50
}

Output includes interaction summaries on each record:

{
  "id": 123,
  "name": "Acme Corp",
  "interactionDates": {
    "lastMeeting": { "date": "2026-01-08T10:00:00Z", "daysSince": 5 },
    "nextMeeting": { "date": "2026-01-20T14:00:00Z", "daysUntil": 7 },
    "lastEmail": { "date": "2026-01-10T09:30:00Z", "daysSince": 3 }
  }
}

Available Expansions¶

Expansion	Description	Supported Entities
`interactionDates`	Last/next meeting, email dates	`persons`, `companies`, `opportunities`, `listEntries`
`unreplied`	Detect unreplied incoming messages (email/chat)	`persons`, `companies`, `opportunities`, `listEntries`

Unreplied Messages Expansion¶

Add unreplied message detection to each record:

{
  "from": "listEntries",
  "where": { "path": "listName", "op": "eq", "value": "Pipeline" },
  "expand": ["unreplied"],
  "limit": 50
}

Output includes unreplied message info on each record:

{
  "id": 123,
  "entity": { "id": 456, "type": "company", "name": "Acme Corp" },
  "unreplied": {
    "date": "2026-01-15T09:30:00Z",
    "daysSince": 4,
    "type": "email",
    "subject": "Re: Partnership opportunity"
  }
}

Records without unreplied messages have "unreplied": null. For chat messages, subject is null.

Expand vs Include¶

Feature	`include`	`expand`
Purpose	Fetch related entities	Add computed data
Output	Separate records	Merged into each record
Example	Related company records	Dates, counts, summaries

Warning: Expand also causes N+1 API calls. For large datasets, consider using list export --expand interactions for streaming output.

Aggregations¶

Basic Aggregates¶

{
  "from": "opportunities",
  "aggregate": {
    "total": { "count": true },
    "totalValue": { "sum": "amount" },
    "avgValue": { "avg": "amount" }
  }
}

Group By¶

{
  "from": "opportunities",
  "groupBy": "status",
  "aggregate": {
    "count": { "count": true },
    "totalValue": { "sum": "amount" }
  }
}

Having (Filter Aggregated Results)¶

{
  "from": "opportunities",
  "groupBy": "status",
  "aggregate": {
    "count": { "count": true }
  },
  "having": { "path": "count", "op": "gte", "value": 5 }
}

Sorting¶

{
  "from": "persons",
  "orderBy": [
    { "field": "lastName", "direction": "asc" },
    { "field": "firstName", "direction": "asc" }
  ]
}

Limiting Results¶

{
  "from": "persons",
  "limit": 100
}

Dry-Run Mode¶

Always preview expensive queries first:

xaffinity query --file query.json --dry-run

Output shows:

Query Execution Plan

Query:
  $version: 1.0
  from: persons
  include: [companies]
  limit: 100

Steps:
  [1] FETCH persons (1 API call)
  [2] FILTER (client-side)
  [3] INCLUDE companies (up to 100 API calls)
  [4] LIMIT 100

Estimated:
  API Calls: 101
  Records: 100

[warning] Include 'companies' will make N API calls (1 per person).

Output Formats¶

# Table (default for interactive)
xaffinity query --file query.json

# JSON output
xaffinity query --file query.json --json

# CSV output
xaffinity query --file query.json --csv

# JSON with metadata
xaffinity query --file query.json --json --include-meta

Command Options¶

Option	Description
`--file`, `-f`	Read query from JSON file
`--query`	Inline JSON query string
`--query-version`	Override `$version` in query
`--dry-run`	Show execution plan without running
`--dry-run-verbose`	Show detailed plan with API call breakdown
`--confirm`	Require confirmation before expensive operations
`--max-records`	Safety limit on total records (default: 10000)
`--timeout`	Overall timeout in seconds (default: 300)
`--json`	Output as JSON (alias for `--output json`)
`--csv`	Output as CSV (alias for `--output csv`)
`--output`	Output format: table, json, csv
`--include-meta`	Include execution metadata in output
`--quiet`, `-q`	Suppress progress output
`--verbose`, `-v`	Show detailed progress

Examples¶

Find VIP Contacts at Tech Companies¶

{
  "from": "persons",
  "where": {
    "and": [
      { "path": "fields.VIP", "op": "eq", "value": true },
      { "path": "email", "op": "is_not_null" }
    ]
  },
  "include": ["companies"],
  "orderBy": [{ "field": "lastName", "direction": "asc" }],
  "limit": 100
}

Pipeline Summary by Status¶

{
  "from": "listEntries",
  "where": { "path": "listId", "op": "eq", "value": 12345 },
  "groupBy": "fields.Status",
  "aggregate": {
    "count": { "count": true },
    "totalValue": { "sum": "fields.Deal Value" }
  }
}

Recent Meetings with Persons¶

Since interactions cannot be queried directly, fetch persons with their recent interactions included:

{
  "from": "persons",
  "include": [
    { "interactions": { "limit": 50, "days": 7 } }
  ],
  "limit": 100
}

Then filter for meetings client-side, or use the CLI with --expand-filter for post-fetch filtering.

Best Practices¶

Start with dry-run for complex queries to see API call estimates
Use limit to avoid fetching too much data
Be specific with where to reduce client-side filtering
Avoid deep includes which cause N+1 API calls
Include $version in saved query files for forward compatibility

Limitations¶

All filtering except listEntries field filters happens client-side
Includes cause N+1 API calls (1 per parent record)
Maximum 10,000 records per query for safety
No cross-entity joins (use includes instead)