$filter嵌套级别，包含$or、$gte$lte运算符

Question

我想过滤 MONGO 数据库中的多嵌套文档

示例 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'"， "代码" ： 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

Answer 2

您需要首先$filtermembers数组，同时您必须使用$map聚合循环过滤members数组，将$filter应用于product_warehouses数组。

最后，您需要将cond与$and，$gt和$eq聚合运算符放在一起，这与预期输出条件相同。

db.collection.aggregate([
{ "$project": {
"members": {
"$filter": {
"input": {
"$map": {
"input": "$members",
"as": "member",
"in": {
"$mergeObjects": [
"$$member",
{ "product_warehouses": {
"$filter": {
"input": "$$member.product_warehouses",
"as": "product",
"cond": {
"$or": [
{ "$lt": ["$$product.available_qty", 50] },
{ "$gt": ["$$product.available_qty", 20] }
]
}
}
}}
]
}
}
},
"as": "member",
"cond": {
"$or": [
{ "$lte": [ "$$member.available_qty", 50 ] },
{ "$gte": [ "$$member.available_qty", 10 ] }
]
}
}
}
}},
{ "$match": { "members": { "$ne": [] } } }
])

试一试

Answer 3

试试下面的查询，它工作得很好，经过了适当的测试

db.test.aggregate(
// Pipeline
[
// Stage 1
{
$unwind: {
path : "$members",
includeArrayIndex : "arrayIndex", // optional
preserveNullAndEmptyArrays : false // optional
}
},
// Stage 2
{
$match: {
$and: [ 
{"members.available_qty": {$gte:10}}, 
{"members.available_qty": {$lte:50}} 
]
}
},
// Stage 3
{
$project: {
"_id" :1, 
"id" :1, 
"name" :1, 
"type" :1, 
"members" : {
"id":1,
"name" :1, 
"sku" : 1, 
"type" :1, 
"parent_id" :1, 
"available_qty" :1, 
"on_hand_qty" :1, 
"outgoing_qty" :1, 
"incoming_qty" :1, 
"shortage_qty" :1, 
"product_warehouses": {
$filter: {
input: "$members.product_warehouses",
as: "item",
cond: {
"$and": [
{ "$gte": [ "$$item.available_qty", 20] },
{ "$lte": [ "$$item.available_qty", 50 ] },
]
}
}
}
},
}
},
// Stage 4
{
$match: {
"members.product_warehouses":{$ne: []}
}
},
// Stage 5
{
$group: {
_id:"$_id",
"id" :{ $last: '$id' }, 
"name" :{ $last: '$name' }, 
"type" : { $last: '$type' }, 
members: { $addToSet: "$members" }
}
},
],

);

Answer 4

您可以使用下面的聚合查询。由于$elemMatch在$project阶段不是有效的运算符，因此您可以将该部分替换为$filter、$size和$gt比较。

{"members":{
"$filter":{
"input":"$members",
"cond":{
"$and":[
{"$gte":["$$this.available_qty",10]},
{"$lte":["$$this.available_qty",50]},
{"$gt":[
{"$size":{
"$filter":{
"input":"$$this.product_warehouses",
"cond":{
"$and":[
{"$gte":["$$this.available_qty",20]},
{"$lte":["$$this.available_qty",50]}
]
}
}
}},
0
]}
]
}
}
}}