MongoDB聚合查询嵌套对象内嵌套数组问题求助

Hey there! Let's work through how to tackle aggregation operations on the nested arrays (oneHour and oneDay) in your Mongoose schema. First, let's restate your schema clearly to align on the data structure we're working with:

const yourSchema = new mongoose.Schema({
  parity: { type: String, unique: true },
  dataSet: {
    oneHour: [{
      open: Number,
      high: Number,
      low: Number,
      close: Number,
      date: Date,
      btcVolume: Number,
      _id: false
    }],
    oneDay: [{
      open: Number,
      high: Number,
      low: Number,
      close: Number,
      date: Date,
      btcVolume: Number,
      _id: false
    }],
    _id: false
  },
  _id: { type: String }
});

Below are common aggregation scenarios you might need, with step-by-step pipeline examples:

1. Filter Nested Array Elements #

If you want to retrieve only specific entries from a nested array (e.g., oneHour entries where the date falls within a range), use the $filter operator in a $project stage:

const pipeline = [
  // Match the parent document first (optional but efficient)
  { $match: { parity: "your-target-parity" } },
  {
    $project: {
      parity: 1,
      filteredOneHour: {
        $filter: {
          input: "$dataSet.oneHour",
          as: "entry",
          cond: {
            $and: [
              { $gte: ["$$entry.date", new Date("2024-01-01")] },
              { $lte: ["$$entry.date", new Date("2024-01-31")] }
            ]
          }
        }
      }
    }
  }
];

// Execute the aggregation
YourModel.aggregate(pipeline)
  .then(results => console.log(results))
  .catch(err => console.error(err));

2. Calculate Aggregations on Nested Array Fields #

To compute stats like average close price, total volume, or max high value for a nested array, use operators like $avg, $sum, $max directly on the array:

Example: Get the average close price and total BTC volume for oneDay entries:

const pipeline = [
  { $match: { parity: "your-target-parity" } },
  {
    $project: {
      parity: 1,
      avgOneDayClose: { $avg: "$dataSet.oneDay.close" },
      totalOneDayVolume: { $sum: "$dataSet.oneDay.btcVolume" },
      maxOneDayHigh: { $max: "$dataSet.oneDay.high" }
    }
  }
];

3. Unwind Nested Arrays for Document-Level Aggregation #

If you need to treat each array entry as a separate document (e.g., group by date across all entries), use $unwind to flatten the array first:

Example: Group oneHour entries by date and calculate daily average close price:

const pipeline = [
  { $match: { parity: "your-target-parity" } },
  // Flatten the oneHour array into individual documents
  { $unwind: "$dataSet.oneHour" },
  // Extract the date part (without time) for grouping
  {
    $addFields: {
      dailyDate: { $dateTrunc: { date: "$dataSet.oneHour.date", unit: "day" } }
    }
  },
  // Group by the truncated date and compute averages
  {
    $group: {
      _id: "$dailyDate",
      avgClose: { $avg: "$dataSet.oneHour.close" },
      totalVolume: { $sum: "$dataSet.oneHour.btcVolume" }
    }
  },
  // Sort results by date
  { $sort: { _id: 1 } }
];

4. Handle Multiple Nested Arrays Simultaneously #

If you need to aggregate both oneHour and oneDay arrays in the same pipeline, you can combine the above techniques. For example, compute stats for both arrays in one projection:

const pipeline = [
  { $match: { parity: "your-target-parity" } },
  {
    $project: {
      parity: 1,
      oneHourStats: {
        avgClose: { $avg: "$dataSet.oneHour.close" },
        totalVolume: { $sum: "$dataSet.oneHour.btcVolume" }
      },
      oneDayStats: {
        avgClose: { $avg: "$dataSet.oneDay.close" },
        totalVolume: { $sum: "$dataSet.oneDay.btcVolume" }
      }
    }
  }
];

Key Notes: #

• Always start with a $match stage to filter parent documents first—this reduces the data processed in subsequent stages and improves performance.
• Use $filter instead of $unwind if you just need to subset the array without flattening it.
• For complex array transformations, $reduce is a powerful operator to iterate over array elements and compute custom values.

If you had a specific aggregation goal in mind (e.g., a particular stat or filtered result), feel free to refine the ask, and we can tweak the pipeline further!

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