MongoDB聚合查询中如何在Lookup里过滤嵌套数组

开发者阿泽

2026-8-11

问题描述

我执行了以下MongoDB聚合查询来关联pages集合：

db.magazine.aggregate([
    {
        $lookup: {
            from: 'pages',
            localField: 'typeId',
            foreignField: 'visibleTo._id',
            as: 'List'
        }
    }
])

但pages集合的visibleTo字段是嵌套数组，当前查询返回的List中，每个page的visibleTo包含了所有对象，我需要仅保留与typeId匹配的visibleTo._id对应的对象。

当前查询结果示例：

{ 
    "_id" : ObjectId("635f6e6902698132d9659c5a"), 
    "name" : "Sports Magazine", 
    "typeId" : ObjectId("635fc99652ecf9a969eed777"),
    "List" : [
        {
            "_id" : ObjectId("63748c1241ccfbc85a49af20"), 
            "pagename" : "cover", 
            "visibleTo" : [
                {
                    "checked" : "No", 
                    "name" : "Media",
                    "_id" : ObjectId("635fc99652ecf9a969eed777")
                }, 
                {
                    "checked" : "Yes",
                    "name" : "GHS", 
                    "_id" : ObjectId("635fc99652ecf9a969eed77c")
                }
            ]
        }, 
        {
            "_id" : ObjectId("637493810ca44ad4d564e7b3"), 
            "pagename" : "back", 
            "visibleTo" : [
                {
                    "checked" : "Yes",  
                    "name" : "Media",
                    "_id" : ObjectId("635fc99652ecf9a969eed777")
                }, 
                {
                    "checked" : "Yes", 
                    "name" : "GHS", 
                    "_id" : ObjectId("635fc99652ecf9a969eed77c")
                }, 
                {
                    "checked" : "Yes", 
                    "name" : "Others",
                    "_id" : ObjectId("635fc99652ecf9a969eed781")
                }
            ],
        }
    ]
}

解决方案

使用带管道的$lookup，在关联阶段同时过滤visibleTo数组，只保留匹配的元素：

db.magazine.aggregate([
    {
        $lookup: {
            from: 'pages',
            let: { magazineTypeId: "$typeId" },
            pipeline: [
                // 匹配包含目标typeId的pages文档
                { $match: { $expr: { $in: ["$$magazineTypeId", "$visibleTo._id"] } } },
                // 过滤visibleTo数组，仅保留_id匹配的元素
                {
                    $addFields: {
                        visibleTo: {
                            $filter: {
                                input: "$visibleTo",
                                cond: { $eq: ["$$this._id", "$$magazineTypeId"] }
                            }
                        }
                    }
                }
            ],
            as: 'List'
        }
    }
])

预期结果

执行上述查询后，List中的每个page对象的visibleTo数组只会保留与typeId匹配的对象：

{ 
    "_id" : ObjectId("635f6e6902698132d9659c5a"), 
    "name" : "Sports Magazine", 
    "typeId" : ObjectId("635fc99652ecf9a969eed777"),
    "List" : [
        {
            "_id" : ObjectId("63748c1241ccfbc85a49af20"), 
            "pagename" : "cover", 
            "visibleTo" : [
                {
                    "checked" : "No", 
                    "name" : "Media",
                    "_id" : ObjectId("635fc99652ecf9a969eed777")
                }
            ]
        }, 
        {
            "_id" : ObjectId("637493810ca44ad4d564e7b3"), 
            "pagename" : "back", 
            "visibleTo" : [
                {
                    "checked" : "Yes",  
                    "name" : "Media",
                    "_id" : ObjectId("635fc99652ecf9a969eed777")
                }
            ]
        }
    ]
}

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