MongoDB Lookup操作中如何过滤嵌套measurements数组？

集合文档结构 #

Shopping_cart 集合 #

{
    "_id" : ObjectId("65ad3725957423378d16ae9e"),
    "shopping_cart_items" : [
        {
            "product_id" : ObjectId("65ab7dd8e1d08cf3b831c145"),
            "unit" : "dz",
            "value" : NumberInt(1),
            "quantity" : NumberInt(3)
        },
        {
            "product_id" : ObjectId("65ac956acda553e3e7a0e1a7"),
            "unit" : "qty",
            "value" : NumberInt(1),
            "quantity" : NumberInt(4)
        }
    ]
}

Product 集合 #

{
    "_id" : ObjectId("65ab7dd8e1d08cf3b831c145"),
    "title" : "Hapus Mango",
    "description" : "This is my Hapus",
    "active" : true,
    "measurements" : [
        {
            "sequence" : NumberInt(1),
            "unit" : "dz",
            "value" : NumberInt(1),
            "selling_price" : 120.0,
            "mrp" : 150.0
        },
        {
            "sequence" : NumberInt(2),
            "unit" : "dz",
            "value" : NumberInt(5),
            "selling_price" : 600.0,
            "mrp" : 650.0,
            "purchased_count" : NumberInt(0)
        }
    ]
}
{
    "_id" : ObjectId("65ab828f96a9e5522dd61da1"),
    "title" : "Fresh Banana",
    "description" : "This is my description",
    "active" : true,
    "measurements" : [
        {
            "sequence" : NumberInt(1),
            "unit" : "kg",
            "value" : NumberInt(1),
            "selling_price" : 120.0,
            "mrp" : 150.0
        },
        {
            "sequence" : NumberInt(2),
            "unit" : "gm",
            "value" : NumberInt(500),
            "selling_price" : 60.0,
            "mrp" : 75.0
        },
        {
            "sequence" : NumberInt(3),
            "unit" : "kg",
            "value" : NumberInt(10),
            "selling_price" : 1200.0,
            "mrp" : 1500.0
        }
    ]
}

原查询的问题 #

原查询核心错误有两点：

1. 在lookup的pipeline中错误使用$details.measurements，此时处理的是product集合文档，直接用$measurements即可。
2. 直接用$eq比对数组与单个值，无法精准筛选嵌套数组内的元素，需要用$filter实现数组元素匹配。

正确的聚合查询语句 #

db.getCollection("shopping_cart").aggregate([
    { "$unwind": "$shopping_cart_items" },
    {
        "$lookup": {
            "from": "product",
            "localField": "shopping_cart_items.product_id",
            "foreignField": "_id",
            "let": {
                "cart_val": "$shopping_cart_items.value",
                "cart_unit": "$shopping_cart_items.unit"
            },
            "pipeline": [
                {
                    "$project": {
                        "title": 1,
                        "measurements": {
                            "$filter": {
                                "input": "$measurements",
                                "as": "m",
                                "cond": {
                                    "$and": [
                                        { "$eq": ["$$m.value", "$$cart_val"] },
                                        { "$eq": ["$$m.unit", "$$cart_unit"] }
                                    ]
                                }
                            }
                        }
                    }
                },
                // 可选：过滤掉无匹配measurements的商品
                { "$match": { "measurements.0": { "$exists": true } } }
            ],
            "as": "product"
        }
    }
])

代码说明 #

1. $unwind：将购物车的shopping_cart_items数组拆分为单个文档，方便逐个关联商品。
2. $lookup：通过product_id与商品_id匹配，关联product集合。
3. $filter：在$project阶段筛选measurements数组，仅保留与购物车项unit和value完全匹配的元素。
4. 可选$match：若需排除未匹配到对应measurements的商品，可添加此阶段过滤空数组结果。

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