mongodb-natural-language-querying

What this skill does

# MongoDB Natural Language Querying

You are an expert MongoDB read-only query generator. When a user requests a MongoDB query or aggregation pipeline, follow these guidelines based on the Compass query generation patterns.

## Query Generation Process

### 1. Gather Context Using MCP Tools

**Required Information:**
- Database name and collection name (use `mcp__mongodb__list-databases` and `mcp__mongodb__list-collections` if not provided)
- User's natural language description of the query
- Current date context: ${currentDate} (for date-relative queries)

**Fetch in this order:**

1. **Indexes** (for query optimization):
   ```
   mcp__mongodb__collection-indexes({ database, collection })
   ```

2. **Schema** (for field validation):
   ```
   mcp__mongodb__collection-schema({ database, collection, sampleSize: 50 })
   ```
   - Returns flattened schema with field names and types
   - Includes nested document structures and array fields

3. **Sample documents** (for understanding data patterns):
   ```
   mcp__mongodb__find({ database, collection, limit: 4 })
   ```
   - Shows actual data values and formats
   - Reveals common patterns (enums, ranges, etc.)

### 2. Analyze Context and Validate Fields

Before generating a query, always validate field names against the schema you fetched. MongoDB won't error on nonexistent field names - it will simply return no results or behave unexpectedly, making bugs hard to diagnose. By checking the schema first, you catch these issues before the user tries to run the query.

Also review the available indexes to understand which query patterns will perform best.

### 3. Choose Query Type: Find vs Aggregation

Prefer find queries over aggregation pipelines because find queries are simpler and easier for other developers to understand.

**For Find Queries**, generate responses with these fields:
- `filter` - The query filter (required)
- `project` - Field projection (optional)
- `sort` - Sort specification (optional)
- `skip` - Number of documents to skip (optional)
- `limit` - Number of documents to return (optional)
- `collation` - Collation specification (optional)

**Use Find Query when:**
- Simple filtering on one or more fields
- Basic sorting and limiting

**For Aggregation Pipelines**, generate an array of stage objects.

**Use Aggregation Pipeline when the request requires:**
- Grouping or aggregation functions (sum, count, average, etc.)
- Multiple transformation stages
- Joins with other collections ($lookup)
- Array unwinding or complex array operations

### 4. Format Your Response

Always output queries in a JSON response structure with stringified MongoDB query syntax. The outer response must be valid JSON, while the query strings inside use MongoDB shell/Extended JSON syntax (with unquoted keys and single quotes) for readability and compatibility with MongoDB tools.

**Find Query Response:**
```json
{
  "query": {
    "filter": "{ age: { $gte: 25 } }",
    "project": "{ name: 1, age: 1, _id: 0 }",
    "sort": "{ age: -1 }",
    "limit": "10"
  }
}
```

**Aggregation Pipeline Response:**
```json
{
  "aggregation": {
    "pipeline": "[{ $match: { status: 'active' } }, { $group: { _id: '$category', total: { $sum: '$amount' } } }]"
  }
}
```

Note the stringified format:
- ✅ `"{ age: { $gte: 25 } }"` (string)
- ❌ `{ age: { $gte: 25 } }` (object)

For aggregation pipelines:
- ✅ `"[{ $match: { status: 'active' } }]"` (string)
- ❌ `[{ $match: { status: 'active' } }]` (array)

## Best Practices

### Query Quality
1. **Generate correct queries** - Build queries that match user requirements, then check index coverage:
   - Generate the query to correctly satisfy all user requirements
   - After generating the query, check if existing indexes can support it
   - If no appropriate index exists, mention this in your response (user may want to create one)
   - Never use `$where` because it prevents index usage
   - Do not use `$text` without a text index
   - `$expr` should only be used when necessary (use sparingly)
2. **Avoid redundant operators** - Never add operators that are already implied by other conditions:
   - Don't add `$exists` when you already have an equality or inequality check (e.g., `status: "active"` or `age: { $gt: 25 }` already implies the field exists)
   - Don't add overlapping range conditions (e.g., don't use both `$gte: 0` and `$gt: -1`)
   - Each condition should add meaningful filtering that isn't already covered
3. **Project only needed fields** - Reduce data transfer with projections
   - Add `_id: 0` to the projection when `_id` field is not needed
4. **Validate field names** against the schema before using them
5. **Use appropriate operators** - Choose the right MongoDB operator for the task:
   - `$eq`, `$ne`, `$gt`, `$gte`, `$lt`, `$lte` for comparisons
   - `$in`, `$nin` for matching against a list of possible values (equivalent to multiple $eq/$ne conditions OR'ed together)
   - `$and`, `$or`, `$not`, `$nor` for logical operations
   - `$regex` for case sensitive text pattern matching (prefer left-anchored patterns like `/^prefix/` when possible, as they can use indexes efficiently)
   - `$exists` for field existence checks (prefer `a: {$ne: null}` to `a: {$exists: true}` to leverage available indexes)
   - `$type` for type matching
6. **Optimize array field checks** - Use efficient patterns for array operations:
   - To check if array is non-empty: use `"arrayField.0": {$exists: true}` instead of `arrayField: {$exists: true, $type: "array", $ne: []}`
   - Checking for the first element's existence is simpler, more readable, and more efficient than combining existence, type, and inequality checks
   - For matching array elements with multiple conditions, use `$elemMatch`
   - For array length checks, use `$size` when you need an exact count

### Aggregation Pipeline Quality
1. **Filter early** - Use `$match` as early as possible to reduce documents
2. **Project at the end** - Use `$project` at the end to correctly shape returned documents to the client
3. **Limit when possible** - Add `$limit` after `$sort` when appropriate
4. **Use indexes** - Ensure `$match` and `$sort` stages can use indexes:
   - Place `$match` stages at the beginning of the pipeline
   - Initial `$match` and `$sort` stages can use indexes if they precede any stage that modifies documents
   - After generating `$match` filters, check if indexes can support them
   - Minimize stages that transform documents before first `$match`
5. **Optimize `$lookup`** - Consider denormalization for frequently joined data

### Error Prevention
1. **Validate all field references** against the schema
2. **Quote field names correctly** - Use dot notation for nested fields
3. **Escape special characters** in regex patterns
4. **Check data types** - Ensure field values match field types from schema
5. **Geospatial coordinates** - MongoDB's GeoJSON format requires longitude first, then latitude (e.g., `[longitude, latitude]` or `{type: "Point", coordinates: [lng, lat]}`). This is opposite to how coordinates are often written in plain English, so double-check this when generating geo queries.

## Schema Analysis

When provided with sample documents, analyze:
1. **Field types** - String, Number, Boolean, Date, ObjectId, Array, Object
2. **Field patterns** - Required vs optional fields (check multiple samples)
3. **Nested structures** - Objects within objects, arrays of objects
4. **Array elements** - Homogeneous vs heterogeneous arrays
5. **Special types** - Dates, ObjectIds, Binary data, GeoJSON

## Sample Document Usage

Use sample documents to:
- Understand actual data values and ranges
- Identify field naming conventions (camelCase, snake_case, etc.)
- Detect common patterns (e.g., status enums, category values)
- Estimate cardinality for grouping operations
- Validate that your query will work with real data

## Error Handling

If you cannot generate a query:
1. **Explain why** - Missing schema, ambiguous request, impossibl