# SearchQuery Deep Dive: Advanced Patterns

Source: https://docs.rushdb.com/2.x/learn/tutorials/search-and-queries/searchquery-advanced-patterns/

This tutorial is a practical companion to [Reusable SearchQuery](/learn/tutorials/search-and-queries/reusable-search-query).

You will compose production-style queries that RushDB teams and power users rely on every day:

- label-based traversal with aliases
- per-record and grouped aggregations
- nested collect structures
- relationship matching and direction filters
- time-series bucketing

## Real-World Scenario

Assume a graph with these labels and relationships:

- `COMPANY`
- `DEPARTMENT`
- `PROJECT`
- `EMPLOYEE`
- `TASK`

Typical topology:

- `COMPANY` -> `DEPARTMENT`
- `DEPARTMENT` -> `PROJECT`
- `PROJECT` -> `EMPLOYEE`
- `PROJECT` -> `TASK`

All examples below use canonical SearchQuery behavior:

- traversal key is the related label itself (for example `PROJECT`)
- use `$count: '*'` for record counts, `$sum: '$record.field'` for sums
- self-group KPI queries omit `limit`

## 1) Reusable Where Blocks

Start by building filter fragments you can reuse across endpoints.

**TypeScript**
```typescript
const activeEngineeringProjects = {
  labels: ['PROJECT'],
  where: {
    status: { $in: ['active', 'planned'] },
    budget: { $gte: 100000 },
    DEPARTMENT: {
      name: 'Engineering'
    }
  },
  orderBy: { budget: 'desc' },
  limit: 25
}

const projects = await db.records.find(activeEngineeringProjects)

````
**Python**
```python
active_engineering_projects = {
  "labels": ["PROJECT"],
  "where": {
    "status": {"$in": ["active", "planned"]},
    "budget": {"$gte": 100000},
    "DEPARTMENT": {
      "name": "Engineering"
    }
  },
  "orderBy": {"budget": "desc"},
  "limit": 25
}

projects = db.records.find(active_engineering_projects)
````

## 2) Relationship Traversal with Aliases

Use aliases to shape related entity output without flattening your schema.

**TypeScript**

```typescript
const projectComp = await db.records.find({
  labels: ['PROJECT'],
  where: {
    DEPARTMENT: {
      $alias: '$department',
      COMPANY: {
        $alias: '$company',
        region: { $in: ['US', 'CA'] }
      }
    },
    EMPLOYEE: {
      $alias: '$employee',
      employmentType: { $in: ['full_time', 'contract'] }
    }
  },
  select: {
    projectId: '$record.$id',
    projectName: '$record.name',
    departmentName: '$department.name',
    companyName: '$company.name',
    headcount: { $count: '$employee' },
    avgSalary: { $avg: '$employee.salary', $precision: 0 }
  },
  limit: 100
})
```

**Python**

```python
project_comp = db.records.find({
    "labels": ["PROJECT"],
    "where": {
        "DEPARTMENT": {
            "$alias": "$department",
            "COMPANY": {
                "$alias": "$company",
                "region": {"$in": ["US", "CA"]}
            }
        },
        "EMPLOYEE": {
            "$alias": "$employee",
            "employmentType": {"$in": ["full_time", "contract"]}
        }
    },
    "select": {
        "projectId": "$record.$id",
        "projectName": "$record.name",
        "departmentName": "$department.name",
        "companyName": "$company.name",
        "headcount": {"$count": "$employee"},
        "avgSalary": {"$avg": "$employee.salary", "$precision": 0}
    },
    "limit": 100
})
```

**Shell**

```bash
curl -X POST "https://api.rushdb.com/api/v1/records/search" \
  -H "Authorization: Bearer $RUSHDB_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "labels": ["PROJECT"],
    "where": {
      "DEPARTMENT": {
        "$alias": "$department",
        "COMPANY": {"$alias": "$company", "region": {"$in": ["US", "CA"]}}
      },
      "EMPLOYEE": {"$alias": "$employee", "employmentType": {"$in": ["full_time", "contract"]}}
    },
    "select": {
      "projectId": "$record.$id",
      "projectName": "$record.name",
      "departmentName": "$department.name",
      "companyName": "$company.name",
      "headcount": {"$count": "$employee"},
      "avgSalary": {"$avg": "$employee.salary", "$precision": 0}
    },
    "limit": 100
  }'
```

## 3) Dimensional GroupBy

Use dimensional grouping for distribution questions such as "how many projects per status?".

**TypeScript**

```typescript
const statusBreakdown = await db.records.find({
  labels: ['PROJECT'],
  where: {
    DEPARTMENT: { name: 'Engineering' }
  },
  select: {
    count: { $count: '*' },
    avgBudget: { $avg: '$record.budget', $precision: 2 }
  },
  groupBy: ['$record.status'],
  orderBy: { count: 'desc' }
})
```

**Python**

```python
status_breakdown = db.records.find({
    "labels": ["PROJECT"],
    "where": {
        "DEPARTMENT": {"name": "Engineering"}
    },
    "select": {
        "count":     {"$count": "*"},
        "avgBudget": {"$avg": "$record.budget", "$precision": 2}
    },
    "groupBy": ["$record.status"],
    "orderBy": {"count": "desc"}
})
```

**Shell**

```bash
curl -X POST "https://api.rushdb.com/api/v1/records/search" \
  -H "Authorization: Bearer $RUSHDB_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "labels": ["PROJECT"],
    "where": {"DEPARTMENT": {"name": "Engineering"}},
    "select": {
      "count":     {"$count": "*"},
      "avgBudget": {"$avg": "$record.budget", "$precision": 2}
    },
    "groupBy": ["$record.status"],
    "orderBy": {"count": "desc"}
  }'
```

Notes:

- dimensional keys must be property refs like `$record.status`
- omit `limit` to return full distribution

## 4) Self-Group KPIs (Single-Row Totals)

Use self-group mode for whole-dataset KPIs.

**TypeScript**

```typescript
const kpi = await db.records.find({
  labels: ['TASK'],
  where: {
    completed: true,
    completedAt: {
      $gte: { $year: 2025, $month: 1, $day: 1 },
      $lt: { $year: 2026, $month: 1, $day: 1 }
    }
  },
  select: {
    completedCount: { $count: '*' },
    totalHours: { $sum: '$record.hours' },
    avgHours: { $avg: '$record.hours', $precision: 2 }
  },
  groupBy: ['completedCount', 'totalHours', 'avgHours'],
  orderBy: { totalHours: 'asc' }
})
```

**Python**

```python
kpi = db.records.find({
    "labels": ["TASK"],
    "where": {
        "completed": True,
        "completedAt": {
            "$gte": {"$year": 2025, "$month": 1, "$day": 1},
            "$lt": {"$year": 2026, "$month": 1, "$day": 1}
        }
    },
    "select": {
        "completedCount": {"$count": "*"},
        "totalHours":     {"$sum": "$record.hours"},
        "avgHours":       {"$avg": "$record.hours", "$precision": 2}
    },
    "groupBy": ["completedCount", "totalHours", "avgHours"],
    "orderBy": {"totalHours": "asc"}
})
```

**Shell**

```bash
curl -X POST "https://api.rushdb.com/api/v1/records/search" \
  -H "Authorization: Bearer $RUSHDB_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "labels": ["TASK"],
    "where": {
      "completed": true,
      "completedAt": {
        "$gte": {"$year": 2025, "$month": 1, "$day": 1},
        "$lt": {"$year": 2026, "$month": 1, "$day": 1}
      }
    },
    "select": {
      "completedCount": {"$count": "*"},
      "totalHours":     {"$sum": "$record.hours"},
      "avgHours":       {"$avg": "$record.hours", "$precision": 2}
    },
    "groupBy": ["completedCount", "totalHours", "avgHours"],
    "orderBy": {"totalHours": "asc"}
  }'
```

Why this shape:

- self-group uses `select` output key names in `groupBy`
- `orderBy` on a `select` key ensures correct full-scan aggregation behavior
- no `limit`

## 5) Nested Collect for Hierarchies

Use `$collect` with `label` to return structured trees for UI rendering — no `$alias` declarations needed. The special alias `$self` refers to the current traversal level.

**TypeScript**

```typescript
const hierarchy = await db.records.find({
  labels: ['COMPANY'],
  where: {
    foundedAt: { $lt: { $year: 2015 } }
  },
  select: {
    companyName: '$record.name',
    departments: {
      $collect: {
        label: 'DEPARTMENT',
        select: {
          name: '$self.name',
          projects: {
            $collect: {
              label: 'PROJECT',
              select: {
                name: '$self.name',
                employees: {
                  $collect: {
                    label: 'EMPLOYEE',
                    where: { isActive: true },
                    select: { name: '$self.name', role: '$self.role' },
                    orderBy: { name: 'asc' }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  limit: 20
})
```

**Python**

```python
hierarchy = db.records.find({
    "labels": ["COMPANY"],
    "where": {
        "foundedAt": {"$lt": {"$year": 2015}}
    },
    "select": {
        "companyName": "$record.name",
        "departments": {
            "$collect": {
                "label": "DEPARTMENT",
                "select": {
                    "name": "$self.name",
                    "projects": {
                        "$collect": {
                            "label": "PROJECT",
                            "select": {
                                "name": "$self.name",
                                "employees": {
                                    "$collect": {
                                        "label": "EMPLOYEE",
                                        "where": {"isActive": True},
                                        "select": {"name": "$self.name", "role": "$self.role"},
                                        "orderBy": {"name": "asc"}
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    "limit": 20
})
```

**Shell**

```bash
curl -X POST "https://api.rushdb.com/api/v1/records/search" \
  -H "Authorization: Bearer $RUSHDB_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "labels": ["COMPANY"],
    "where": {
      "foundedAt": {"$lt": {"$year": 2015}}
    },
    "select": {
      "companyName": "$record.name",
      "departments": {
        "$collect": {
          "label": "DEPARTMENT",
          "select": {
            "name": "$self.name",
            "projects": {
              "$collect": {
                "label": "PROJECT",
                "select": {
                  "name": "$self.name",
                  "employees": {
                    "$collect": {
                      "label": "EMPLOYEE",
                      "where": {"isActive": true},
                      "select": {"name": "$self.name", "role": "$self.role"},
                      "orderBy": {"name": "asc"}
                    }
                  }
                }
              }
            }
          }
        }
      }
    },
    "limit": 20
  }'
```

Key points:

- `label` traverses inline to a related label — no `$alias` in `where` required
- `$self` is the alias for the current traversal level in `select` expressions
- add `where` inside any `$collect` level to filter that hop (flat property conditions only)
- nest `$collect` inside `select` to any depth

## 6) Relationship Matching and Direction

Use `$relation` when you need to constrain traversal edge type and direction.

**TypeScript**
```typescript
const authoredPosts = await db.records.find({
  labels: ['USER'],
  where: {
    POST: {
      $alias: '$post',
      $relation: { type: 'AUTHORED', direction: 'out' },
      title: { $contains: 'searchquery' }
    }
  },
  select: {
    userName: '$record.name',
    authoredPosts: { $count: '$post' }
  },
  limit: 50
})
```
**Python**
```python
authored_posts = db.records.find({
    "labels": ["USER"],
    "where": {
        "POST": {
            "$alias": "$post",
            "$relation": {"type": "AUTHORED", "direction": "out"},
            "title": {"$contains": "searchquery"}
        }
    },
    "select": {
        "userName": "$record.name",
        "authoredPosts": {"$count": "$post"}
    },
    "limit": 50
})
```

### Variable-length traversal

Add `hops` to `$relation` to match records up to N hops away — a number means exactly N hops, `{ min, max }` a range (`min` defaults to 1). `type` and `direction` apply to every hop; property criteria filter only the final record.

**TypeScript**
```typescript
// Everyone in Alice's reporting chain, up to 4 levels deep
const chain = await db.records.find({
  labels: ['EMPLOYEE'],
  where: {
    EMPLOYEE: {
      $relation: { type: 'REPORTS_TO', direction: 'out', hops: { min: 1, max: 4 } },
      name: { $contains: 'Alice' }
    }
  },
  limit: 50
})
```
**Python**
```python
# Everyone in Alice's reporting chain, up to 4 levels deep
chain = db.records.find({
    "labels": ["EMPLOYEE"],
    "where": {
        "EMPLOYEE": {
            "$relation": {"type": "REPORTS_TO", "direction": "out", "hops": {"min": 1, "max": 4}},
            "name": {"$contains": "Alice"}
        }
    },
    "limit": 50
})
```

### Cycle detection

Use `$cycle` to find records on a closed path back to themselves. It is a record-level predicate whose value is the traversal spec itself — `type`, `direction`, `hops` — with `hops` mandatory (`min` ≥ 2, defaults to 2). It accepts nothing else — no `$alias`, property criteria, or nested labels.

**TypeScript**
```typescript
// Accounts participating in a directed transfer ring of 2–6 hops
const rings = await db.records.find({
  labels: ['ACCOUNT'],
  where: {
    $cycle: { type: 'TRANSFERRED_TO', direction: 'out', hops: { min: 2, max: 6 } }
  },
  limit: 50
})
```
**Python**
```python
# Accounts participating in a directed transfer ring of 2-6 hops
rings = db.records.find({
    "labels": ["ACCOUNT"],
    "where": {
        "$cycle": {"type": "TRANSFERRED_TO", "direction": "out", "hops": {"min": 2, "max": 6}}
    },
    "limit": 50
})
```

Full semantics — hop caps, untyped traversal, aggregation caveats: [Where Operators](/learn/search-query/where-operators#variable-length-traversal-hops).

## 7) Time Bucketing for Trends

Use `timeBucket` to build trend charts directly from query output.

**TypeScript**

```typescript
const monthlyCompletions = await db.records.find({
  labels: ['TASK'],
  where: {
    completed: true
  },
  select: {
    month: { $timeBucket: { field: '$record.completedAt', unit: 'month' } },
    count: { $count: '*' }
  },
  groupBy: ['month'],
  orderBy: { month: 'asc' }
})
```

**Python**

```python
monthly_completions = db.records.find({
    "labels": ["TASK"],
    "where": {
        "completed": True
    },
    "select": {
        "month": {"$timeBucket": {"field": "$record.completedAt", "unit": "month"}},
        "count": {"$count": "*"}
    },
    "groupBy": ["month"],
    "orderBy": {"month": "asc"}
})
```

**Shell**

```bash
curl -X POST "https://api.rushdb.com/api/v1/records/search" \
  -H "Authorization: Bearer $RUSHDB_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "labels": ["TASK"],
    "where": {"completed": true},
    "select": {
      "month": {"$timeBucket": {"field": "$record.completedAt", "unit": "month"}},
      "count": {"$count": "*"}
    },
    "groupBy": ["month"],
    "orderBy": {"month": "asc"}
  }'
```

## 8) Adoption Workflow for Teams

A high-signal workflow when introducing SearchQuery to a team:

1. Start with `records.find` listing queries.
2. Move the same `where` block to `records.delete` for controlled cleanups.
3. Add aliases and per-record `select` expressions.
4. Promote to dimensional `groupBy` for dashboards.
5. Convert to self-group KPI queries for single-row metrics.
6. Introduce nested `collect` when API responses must map directly to UI trees.

## 9) Validation Checklist

Before shipping a query:

- `groupBy` exists only when `select` is present
- each aggregation expression uses `$`-prefixed operator keys
- traversal uses label keys, not custom traversal operators
- date ranges use component objects when comparing
- KPI/self-group queries do not use `limit`
- self-group queries order by a `select` output key

## Wrap-Up

You now have practical patterns for composing SearchQuery in real applications.

Recommended next practice:

- clone one of your existing list queries
- add aliases and per-record `select` expressions
- then progressively transform it into dimensional and self-group analytics
