MongoDB聚合查询：如何合并多文档筛选后的K-data数组

Hey there! Let's tackle this MongoDB aggregation problem you're facing. Since you're new to MongoDB, I'll break this down step by step so it's easy to follow.

Step-by-Step Explanation #

You've already nailed the first part: using $match and $filter to isolate K-data entries within your target date range. The missing piece is combining all those separate filtered arrays into one unified list. We've got two solid approaches for this—let's cover both.

Example Context #

First, let's align on sample data and your current query to make the solution concrete.

Original Sample Data #

Assuming your documents look something like this (adjust to match your actual structure):

[
  {
    "_id": 1,
    "asset": "StockA",
    "K-data": [
      {"date": "2020-01-01", "open": 25, "close": 26},
      {"date": "2020-01-06", "open": 26, "close": 27},
      {"date": "2020-02-04", "open": 28, "close": 29}
    ]
  },
  {
    "_id": 2,
    "asset": "StockB",
    "K-data": [
      {"date": "2020-01-04", "open": 15, "close": 16},
      {"date": "2020-01-22", "open": 16, "close": 17},
      {"date": "2020-02-02", "open": 17, "close": 18}
    ]
  }
]

Your Existing Query (Returns Split Arrays) #

I'm guessing your current aggregation pipeline looks similar to this—it filters the K-data but leaves you with separate arrays per document:

db.yourCollection.aggregate([
  // Match documents that might contain relevant K-data (optimizes performance)
  {
    $match: {
      "K-data.date": {
        $gte: "2020-01-03",
        $lte: "2020-02-03"
      }
    }
  },
  // Filter the K-data array to keep only entries in the date range
  {
    $project: {
      filteredKData: {
        $filter: {
          input: "$K-data",
          cond: {
            $and: [
              {$gte: ["$$this.date", "2020-01-03"]},
              {$lte: ["$$this.date", "2020-02-03"]}
            ]
          }
        }
      }
    }
  }
])

Solution 1: $group + $concatArrays (Clean Array Merge) #

This is the most straightforward method if you just need to merge existing filtered arrays without modifying individual entries. Add a $group stage to combine all arrays into one:

db.yourCollection.aggregate([
  {
    $match: {
      "K-data.date": {
        $gte: "2020-01-03",
        $lte: "2020-02-03"
      }
    }
  },
  {
    $project: {
      filteredKData: {
        $filter: {
          input: "$K-data",
          cond: {
            $and: [
              {$gte: ["$$this.date", "2020-01-03"]},
              {$lte: ["$$this.date", "2020-02-03"]}
            ]
          }
        }
      }
    }
  },
  // Group all documents and concatenate their filtered arrays
  {
    $group: {
      _id: null, // Group every document together
      mergedKData: {
        $concatArrays: ["$filteredKData"]
      }
    }
  },
  // Optional: Remove the auto-generated _id field for cleaner output
  {
    $project: {
      _id: 0,
      mergedKData: 1
    }
  }
])

Solution 2: $unwind + $group (For Entry-Level Processing) #

If you want to manipulate individual K-data entries (like sorting, adding calculated fields) before merging, use $unwind to break arrays into single-entry documents, then group them back:

db.yourCollection.aggregate([
  {
    $match: {
      "K-data.date": {
        $gte: "2020-01-03",
        $lte: "2020-02-03"
      }
    }
  },
  // Split K-data array into individual documents
  {
    $unwind: "$K-data"
  },
  // Filter individual entries (since $match only checks if any entry in the array qualifies)
  {
    $match: {
      "K-data.date": {
        $gte: "2020-01-03",
        $lte: "2020-02-03"
      }
    }
  },
  // Optional: Add sorting if you need entries in date order
  {
    $sort: {"K-data.date": 1}
  },
  // Group all entries into a single array
  {
    $group: {
      _id: null,
      mergedKData: {
        $push: "$K-data"
      }
    }
  },
  {
    $project: {
      _id: 0,
      mergedKData: 1
    }
  }
])

Expected Output #

Both solutions will return a single document with your merged K-data array:

{
  "mergedKData": [
    {"date": "2020-01-06", "open": 26, "close": 27},
    {"date": "2020-01-04", "open": 15, "close": 16},
    {"date": "2020-01-22", "open": 16, "close": 17},
    {"date": "2020-02-02", "open": 17, "close": 18}
  ]
}

Quick Tips for New MongoDB Users #

• Date Storage: If you're doing frequent date operations, store dates as MongoDB Date objects (not strings) to avoid comparison errors. If you must use strings, stick to ISO format (YYYY-MM-DD) so lexicographical sorting works correctly.
• Performance: Always start with $match to filter out irrelevant documents early—it reduces the data pipeline has to process later.

内容的提问来源于stack exchange，提问作者HARZI