MongoDB：含$unwind的聚合查询转find方法及性能对比咨询

Alright, let's break this down step by step. First, converting that aggregation query to a find() is totally doable—we just need to leverage MongoDB's array query operators instead of unwinding the array first.

Rewriting as a find() Query #

Your original aggregation is looking for documents where the values array contains at least one element that meets three criteria:

1. timestamp exists
2. sensor equals "V1"
3. timestamp is greater than or equal to your specified date

We can combine all these into a single find() query using $elemMatch to ensure we're matching array elements that satisfy all conditions at once:

// Basic find query (returns entire matching documents)
db.cms.find(
  {
    "values": {
      "$elemMatch": {
        "timestamp": { "$exists": true, "$gte": "2018-02-07 14:00..." },
        "sensor": "V1"
      }
    }
  }
)

If you only want to return the matching values elements (not the entire document), use projection:

• For the first matching element (works in all MongoDB versions):

  db.cms.find(
    {
      "values": {
        "$elemMatch": {
          "timestamp": { "$exists": true, "$gte": "2018-02-07 14:00..." },
          "sensor": "V1"
        }
      }
    },
    { "values.$": 1 } // Projects only the first matching array element
  )
  

• For all matching elements (MongoDB 3.2+), use $filter in the projection:

  db.cms.find(
    {
      "values": {
        "$elemMatch": {
          "timestamp": { "$exists": true, "$gte": "2018-02-07 14:00..." },
          "sensor": "V1"
        }
      }
    },
    {
      "values": {
        "$filter": {
          "input": "$values",
          "as": "val",
          "cond": {
            "$and": [
              { "$exists": ["$$val.timestamp", true] },
              { "$eq": ["$$val.sensor", "V1"] },
              { "$gte": ["$$val.timestamp", "2018-02-07 14:00..."] }
            ]
          }
        }
      }
    }
  )
  

Performance Comparison: find() vs. Original Aggregation #

Yes, find() will almost certainly outperform your original aggregation query—here's why:

• Your original aggregation uses $unwind, which takes every document with a values array and creates a new document for each element in that array. If your arrays are large, this can explode the number of documents MongoDB has to process, leading to excessive memory usage and slow execution.
• A properly indexed find() query matches documents directly without unwinding. For this case, create a compound index like:

  db.cms.createIndex({ "values.sensor": 1, "values.timestamp": 1 })
  

  This index lets MongoDB quickly locate documents with matching array elements, avoiding the need to scan every element in every array.

A Note if You Still Need Aggregation Output Format #

If you need the "one document per matching array element" format that $unwind provides, you can optimize your aggregation by moving the $match stages before $unwind to reduce the number of documents being unwound:

db.cms.aggregate([
  // First filter down to only relevant documents
  { "$match": { "values.sensor": "V1" } },
  { "$match": { "values.timestamp": { "$exists": 1, "$gte": "2018-02-07 14:00..." } } },
  // Now unwind only the filtered documents
  { "$unwind": "$values" },
  // Final match to filter individual unwound elements
  { "$match": { "values.sensor": "V1", "values.timestamp": { "$exists": 1, "$gte": "2018-02-07 14:00..." } } }
])

This will drastically cut down on the number of documents processed in the $unwind stage, making the aggregation much faster.

内容的提问来源于stack exchange，提问作者Victor W.