MongoDB:通过数组大小和aggregate进行过滤

Question

我使用聚合查询。我需要返回一个文档，如果:

• "total"不存在
• 数组大小小于"total">

隐藏所有数组等于为"total"的文档。

指出:

• "container"可以有不同的"子容器">
• "subContainer"是动态密钥名
• "total"是可选的
• "arr"size <= "total">

Documents in collection:

[
{  // "container.subContainer2.arr" is lower then "total"
"_id": 1,
"title": "title1",
"container": {
"subContainer1": {
"arr": [1, 2, 3]
},
"subContainer2": {
"arr": [1]
}
},
"total": 3
},
{  // "total" unknow
"_id": 2,
"title": "title2",
"container": {
"subContainer3": {
"arr": [1, 2, 3, 4]
}
}
},
{  // both array size are equal to "total"
"_id": 3,
"title": "title3",
"container": {
"subContainer4": {
"arr": [1, 2, 3, 4, 5]
},
"subContainer5": {
"arr": [1, 2, 3, 4, 5]
}
},
"total": 5
}
]

输出:

[
{
"title": "title1"
},
{
"title": "title2"
}
]

Answer 1

首先我们用$addFields添加一些额外的字段并使用map创建包含arr的数组每个文档中arr的sum大小作为newField创建状态字段为布尔值，如果"arr" size <= "total"和match所有文件状态:true和项目标题

db.collection.aggregate([
{
"$addFields": {
"newField": {
"$objectToArray": "$container"
}
}
},
{
"$addFields": {
"newField1": {
"$map": {
"input": "$newField",
"as": "z",
"in": {
$size: "$$z.v.arr"
}
}
}
}
},
{
"$addFields": {
"newField": {
"$sum": "$newField1"
}
}
},
{
"$addFields": {
"status": {
$gte: [
"$total",
"$newField"
]
}
}
},
{
$match: {
status: true
}
},
{
$project: {
title: 1,
_id: 0
}
}
])

https://mongoplayground.net/p/PhkeJUCkE6d

Answer 2

我使用$not检查total是否存在(false)和$cond检查结果来解决这个问题。

[
{
"$addFields": {
"newField": {
"$objectToArray": "$container"
}
}
},
{
"$addFields": {
"newField1": {
"$map": {
"input": "$newField",
"as": "z",
"in": {
"$size": "$$z.v.arr"
}
}
}
}
},
{
"$addFields": {
"newField": {
"$min": "$newField1"
}
}
},
{
"$addFields": {
"status": {
"$cond": [
{
"$not": [
"$total"
]
},
true,
{
"$gt": [
"$total",
"$newField"
]
}
]
}
}
},
{
"$match": {
"status": true
}
},
{
"$project": {
"title": 1,
"_id": 0
}
}
]

https://mongoplayground.net/p/qrSiAX88UFZ