mongodb - $filter 嵌套级别与 $or、$gte、$lte 运算符

Question

我想过滤 MONGO db 中的多重嵌套文档

示例 JSON:

{ 
"_id" : ObjectId("5b5c3afbcc43cb5ed64b7a04"), 
"id" : NumberLong(15015060), 
"name" : "1801_Conf", 
"type" : NumberInt(2), 
"members" : [
    {
        "id" : NumberLong(15015061), 
        "name" : "1801_Conf-W--", 
        "sku" : "1801_new", 
        "type" : NumberInt(1), 
        "parent_id" : NumberLong(15015060), 
        "available_qty" : NumberInt(10), 
        "on_hand_qty" : NumberInt(10), 
        "outgoing_qty" : NumberInt(0), 
        "incoming_qty" : NumberInt(0), 
        "shortage_qty" : NumberInt(0), 
        "product_warehouses" : [
            {
                "warehouse_id" : NumberLong(3), 
                "available_qty" : NumberInt(10), 
                "outgoing_qty" : NumberInt(0), 
                "incoming_qty" : NumberInt(0)
            },
            {
                "warehouse_id" : NumberLong(4), 
                "available_qty" : NumberInt(600), 
                "outgoing_qty" : NumberInt(0), 
                "incoming_qty" : NumberInt(0)
            }
        ], 
        ]
    } 
] 

}

预期输出:只想过滤 available_qty < 50 且 > 10 且members.product_warehouses.available_qty < 50 且 > 20 的成员(不是全部)

查询:

db.products.aggregate([{
   "$match": {
       "tenant_id": XXX,
       "type" : 2
   }
}, {
   "$project": {
       "name": 1,
       "sku": 1,
       "members": {
           "$filter": {
               "input": "$members",
               "as": "member",
               "cond": {

                       "$and": 
                        [
                            {
                               "$gte": ["$$member.product_warehouses.available_qty", 10]
                            }, 
                            {
                               "$lte": ["$$member.available_qty", 50]
                            },
                            {
                               "product_warehouses": {
                               "$elemMatch" : {

                                   }
                               }
                            }
                        ]

               }
           }
       }
   }
}])

错误: : { "ok": 0, "errmsg": "无效运算符 '$elemMatch'", "code": 15999 } : 聚合失败

Answer 1

据我了解，这是您需要的查询。

db.col.aggregate([
    {"$unwind" : "$members"},
    {"$match" : {"$and" : [{"members.available_qty" : {"$lt" : 50}},{"members.available_qty" : {"$gt" : 10}}]}},
    {"$match" : {"$and" : [{"members.product_warehouses.available_qty" : {"$lt" : 50}},{"members.product_warehouses.available_qty" : {"$gt" : 20}}]}},
    {"$group" : {"_id" : "$_id",
        "type" : {"$first" : "$type"},
        "name" : {"$first" : "$name"},
        "id" : {"$first" : "$id"},
        "members" : {"$addToSet" : "$members"}
        }
    }
]);

或

db.lo.aggregate([
{"$unwind" : "$members"},
{"$match" : {
    "$and" : [
        {"members.available_qty" : {"$lt" : 50}},
        {"members.available_qty" : {"$gt" : 10}}
        ]
    }
},
{"$match" : { 
    "members.product_warehouses": { 
        "$elemMatch":{ 
            "available_qty": { "$lt": 50 }, 
            "available_qty": { "$gt": 20 }
        } 
    } 
}},
{"$group" : {"_id" : "$_id",
    "type" : {"$first" : "$type"},
    "name" : {"$first" : "$name"},
    "id" : {"$first" : "$id"},
    "members" : {"$addToSet" : "$members"}
    }
}

]);

如果任何一个子文档与以下条件匹配members.product_warehouses.available_qty > 20且members.product_warehouses.available_qty < 50，它将返回整个product_warehouses数组，与相同$elemMatch