By name: mongodb-queries description: Load when writing or debugging MongoDB find or aggregate queries, especially table-friendly output, pivots, dotted keys, date bucketing, projections, limits, and aggregation pipelines. entities: - mongodb - mongo - aggregate - aggregation - pipeline - pivot - dotted keys - dateToString
MongoDB query requirements
Structure MongoDB query results to be flat and table-friendly by default.
| Requirement | Do (best practice) | Do not |
|---|---|---|
| Pivot time-series data | Return one document per entity, with periods as field names ("2024-01", "2024-02"). |
Return separate documents per month/quarter/year when the user expects a table. |
| Flat output | Use clear top-level identifier fields (product, customer_id). |
Return nested objects or arrays in the final output. |
| Column naming | Prefer snake_case via $project, $addFields, or $replaceRoot. Keep dynamic period keys as-is. |
Use camelCase or names with spaces in output columns. |
| Column order | Use $replaceRoot as the final stage to set a logical key order. |
Rely on $project when exact order matters. |
| Missing gaps | If pivoting time series, fill missing periods with 0 or null. |
Leave gaps in time-series data. |
| Dotted keys | Access field names containing dots with $getField. |
Use standard dot notation ("$user.name") for literal dotted keys. |
Example: monthly sales pivot
// db: ecommerce
db.orders.aggregate([
{
$match: {
status: "completed",
orderDate: { $gte: ISODate("2024-01-01"), $lt: ISODate("2025-01-01") }
}
},
{
$group: {
_id: {
product: "$productName",
month: { $dateToString: { format: "%Y-%m", date: "$orderDate" } }
},
total_sales: { $sum: "$saleAmount" }
}
},
{
$group: {
_id: "$_id.product",
monthly_sales: { $push: { k: "$_id.month", v: "$total_sales" } }
}
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: [ { product: "$_id" }, { $arrayToObject: "$monthly_sales" } ]
}
}
}
]).limit(500);
This pivots monthly sales so each product is one row with months as columns.