如何使用Mongoose Aggregate填充嵌套数组中的关联字段？

我正在学习Mongoose，尝试使用aggregate函数填充Account Schema的所有关联数据。当前在填充$account.sessions.trades.closes.timeframe和$account.sessions.trades.tags字段时遇到困难，期望查询结果达到如下格式：

[
{ // trade 1
  name : "account-name",
  session : {name : "session-name"},
  trade : {
     symbol : {...},
     open : {....},
     closes : {
       gain : "12",
       timeframe : {time : 12, unit : "min"}
     },
     tags  : [
       {name: "tag-name-1"},
       {name: "tag-name-2"}
     ],

  }
},
{ // trade 2
 ...
},
{ // trade 3 
...
}
]

各Schema定义 #

Account #

const accountSchema = new Schema({
    name     : { type : String, required: true},
    sessions : [{
        type : Schema.Types.ObjectId,
        ref  : "Session_Model"
    }],
});

Session #

const sessionSchema = new Schema({
    name         : {type : String}, 
    trades       : [{
        type : Schema.Types.ObjectId,
        ref  : "Trade_Model"
    }],
});

Trade #

var operationSchema = new Schema({
    gain        : { type: Number },
    timeframe   : { type: Schema.Types.ObjectId, ref : "Timeframe_Model"},
});

const tradeSchema = new Schema({
    symbol          : { type: Schema.Types.ObjectId, ref : "Symbol_Model"},
    open            : operationSchema,
    closes          : [operationSchema],
    tags            : [{type: Schema.Types.ObjectId, ref : "Tag_Model"}],
});

Timeframe #

const timeframeSchema = new Schema({
    time       : { type: Number, required: true },
    unit       : { type: String, required: true },
});

当前使用的aggregate查询代码 #

Account_Model.aggregate(
                [
                    {$lookup : {
                        from         : "session_models",
                        localField   : "sessions",
                        foreignField : "_id",
                        as           : "populate_sessions"
                    }},
                    {$lookup : {
                        from         : "trade_models",
                        localField   : "populate_sessions.trades",
                        foreignField : "_id",
                        as           : "trade"
                    }},
                    
                    
                    {$unwind  : "$trade"},
                    {$sort    : {"trade.open.date" : 1}},

                    {$skip    : Number(req.params.limit) * (Number(req.params.page)-1)},   
                    {$limit   : Number(req.params.limit)},   

                    {$lookup : {
                        from         : "symbol_models",
                        localField   : "trade.symbol",
                        foreignField : "_id",
                        as           : "trade.symbol"
                    }},      
                    {$unwind : { path : "$trade.symbol"}},
                    {$lookup : {
                        from         : "timeframe_models",
                        localField   : "trade.open.timeframe",
                        foreignField : "_id",
                        as           : "trade.open.timeframe"
                    }},
                    {$unwind : { path : "$trade.open.timeframe"}},
                    {$lookup : {
                        from         : "session_models",
                        localField   : "trade.session_id",
                        foreignField : "_id",
                        as           : "session"
                    }},
                    {$unwind : { path : "$session"}},
                    {$match   : {}},
                    {$project : {
                        "name"          : 1,           
                        "trade"         : 1, 
                        "session.name"  : 1,                        
                        }
                    }
                ]
            ).then(accounts => { ... })

要解决closes.timeframe和tags的填充问题，需在aggregate流程中添加对应的$lookup操作，并处理数组嵌套的特殊情况，具体修改如下：

1. 填充Trade的tags字段 #

在填充完trade.symbol后，添加针对tags的$lookup操作，由于期望tags以数组形式返回，无需额外$unwind：

{$lookup: {
    from: "tag_models",
    localField: "trade.tags",
    foreignField: "_id",
    as: "trade.tags"
}}

2. 填充closes数组中的timeframe字段 #

由于closes是数组类型，需先展开数组再填充关联数据，最后重新分组恢复数组结构：

// 展开closes数组，保留空数组或null的情况
{$unwind: {path: "$trade.closes", preserveNullAndEmptyArrays: true}},
// 填充单条close的timeframe
{$lookup: {
    from: "timeframe_models",
    localField: "trade.closes.timeframe",
    foreignField: "_id",
    as: "trade.closes.timeframe"
}},
// 展开timeframe数组为单个对象
{$unwind: {path: "$trade.closes.timeframe", preserveNullAndEmptyArrays: true}},
// 重新分组，恢复trade的closes数组结构
{$group: {
    _id: "$_id",
    name: {$first: "$name"},
    session: {$first: "$session"},
    trade: {$first: {
        symbol: "$trade.symbol",
        open: "$trade.open",
        tags: "$trade.tags",
        closes: {$push: "$trade.closes"}
    }}
}}

完整修改后的aggregate代码 #

Account_Model.aggregate([
    {$lookup: {
        from: "session_models",
        localField: "sessions",
        foreignField: "_id",
        as: "populate_sessions"
    }},
    {$lookup: {
        from: "trade_models",
        localField: "populate_sessions.trades",
        foreignField: "_id",
        as: "trade"
    }},
    {$unwind: "$trade"},
    {$sort: {"trade.open.date": 1}},
    {$skip: Number(req.params.limit) * (Number(req.params.page)-1)},   
    {$limit: Number(req.params.limit)},   
    // 填充symbol
    {$lookup: {
        from: "symbol_models",
        localField: "trade.symbol",
        foreignField: "_id",
        as: "trade.symbol"
    }},      
    {$unwind: {path: "$trade.symbol"}},
    // 填充open.timeframe
    {$lookup: {
        from: "timeframe_models",
        localField: "trade.open.timeframe",
        foreignField: "_id",
        as: "trade.open.timeframe"
    }},
    {$unwind: {path: "$trade.open.timeframe"}},
    // 填充tags
    {$lookup: {
        from: "tag_models",
        localField: "trade.tags",
        foreignField: "_id",
        as: "trade.tags"
    }},
    // 处理closes数组并填充timeframe
    {$unwind: {path: "$trade.closes", preserveNullAndEmptyArrays: true}},
    {$lookup: {
        from: "timeframe_models",
        localField: "trade.closes.timeframe",
        foreignField: "_id",
        as: "trade.closes.timeframe"
    }},
    {$unwind: {path: "$trade.closes.timeframe", preserveNullAndEmptyArrays: true}},
    // 恢复closes数组结构
    {$group: {
        _id: "$_id",
        name: {$first: "$name"},
        session: {$first: "$session"},
        trade: {$first: {
            symbol: "$trade.symbol",
            open: "$trade.open",
            tags: "$trade.tags",
            closes: {$push: "$trade.closes"}
        }}
    }},
    // 关联session（需确保trade.session_id字段存在，否则需调整关联逻辑）
    {$lookup: {
        from: "session_models",
        localField: "trade.session_id",
        foreignField: "_id",
        as: "session"
    }},
    {$unwind: {path: "$session"}},
    {$project: {
        "name": 1,           
        "trade": 1, 
        "session.name": 1                        
    }}
]).then(accounts => { ... })

注意事项 #

• 确认集合名称与Mongoose自动生成的一致（通常为模型名小写复数形式，如tag_models）。
• 使用preserveNullAndEmptyArrays: true避免因空数组或缺失字段导致数据丢失。
• 若trade.session_id字段不存在，需调整session关联逻辑，例如从populate_sessions中匹配对应session。

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