英文:
mongoDB count deeply nested elements on more then one condition
问题
请帮忙，我需要找到具有以下条件之一的所有_a[]._p[]元素的count()：
-
_a[]._p[].s.d.t[].dateP=2022 并且 _a[]._p[].s.d.t[].tF="N"
-
_a[]._p[].s.c.t[].dateP=2022 并且 _a[]._p[].s.c.t[].tF="N"
在以下类型的文档中：
{
"_id": ObjectId("5c05984246a0201286d4b57a"),
"f": "x",
"_a": [
{
"_p": [
{
"pid": 2,
"s": {
"d": {
"t": [
{
"id": 1,
"dateP": "20200-09-20"
},
{
"id": 2,
"dateP": "2022-09-20",
"tF": "N"
}
]
},
"c": {
"t": [
{
"id": 3,
"dateP": "20300-09-22"
},
{
"id": 4,
"dateP": "2022-09-23",
"tF": "N"
}
]
}
}
}
]
}
]
}
在我的尝试中，我只能计算部分匹配条件的文档，但不确定在有更多嵌套数组的情况下是否正确，并不确定如何更快地计算_a内的_p元素：
db.collection.count({ "_a._p.s.c.t": { $elemMatch: { tF: "N", dateP: /^2022/i } } })
从Playground中预期的结果应如下所示：
{ total: 1 }
因为_a._p具有与上述条件匹配的s.d.t的id：2和s.c.t的id：4。
英文:
Please, help , I need to find count() of all _a[]._p[] elements having at least one of:
_a[]._p[].s.d.t[].dateP=2022 and _a[]._p[].s.d.t[].tF="N"
and
_a[]._p[].s.c.t[].dateP=2022 and _a[]._p[].s.c.t[].tF="N"
in following type of document:
{
"_id": ObjectId("5c05984246a0201286d4b57a"),
f: "x",
"_a": [
{
"_p": [
{
"pid": 2,
"s": {
"d": {
"t": [
{
id: 1,
"dateP": "20200-09-20",
},
{
id: 2,
"dateP": "2022-09-20",
"tF": "N"
}
]
},
"c": {
"t": [
{
id: 3,
"dateP": "20300-09-22"
},
{
id: 4,
"dateP": "2022-09-23",
"tF": "N"
}
]
}
}
}
]
}
]
}
In my attempt I can count only the documents that partially match the condition , but not sure if this is correct when there is more nested arrays and not sure how to do it faster and count the _p elements inside the _a:
db.collection.count({ "_a._p.s.c.t":{ $elemMatch:{ tF:"N" , dateP: /^2022/i } } })
The expected result from playground need to look as follow:
{ total: 1 }
Since the _a._p having s.d.t with id:2 and s.c.t with id:4 match the above condition
答案1
得分: 1
更新：仅计算元素
db.collection.aggregate([
{
$project: {
sct: {
"$size": "$_a._p.s.c.t"
},
scd: {
"$size": "$_a._p.s.d.t"
}
}
},
])
<details>
<summary>英文:</summary>
and the solution should be like:
_update: only count element_
db.collection.aggregate([
{
$project: {
sct: {
"$size": "$_a._p.s.c.t"
},
scd: {
"$size": "$_a._p.s.d.t"
}
}
},
])
[MONGO_PALYGROUND](https://mongoplayground.net/p/P8wVsZlIfPD)
</details>
# 答案2
**得分**: 1
这是一个方法，可以在不使用`$unwind`的情况下执行，尽管`$reduce`的嵌套级别似乎非常容易出错。我希望在依赖它之前，你能用大量数据测试它。
```mongodb
db.collection.aggregate([
{
"$match": {
"_a._p.s.d.t": {
"$elemMatch": {
"dateP": {"$regex": "^2022"},
"tF": "N"
}
},
"_a._p.s.c.t": {
"$elemMatch": {
"dateP": {"$regex": "^2022"},
"tF": "N"
}
}
}
},
{
"$project": {
"ap": "$_a._p"
}
},
{
"$project": {
"docCount": {
"$reduce": {
"input": "$ap",
"initialValue": 0,
"in": {
"$sum": [
"$$value",
{
"$reduce": {
"input": "$$this",
"initialValue": 0,
"in": {
"$sum": [
"$$value",
{
"$cond": [
{
"$and": [
{
"$reduce": {
"input": "$$this.s.c.t",
"initialValue": false,
"in": {
"$or": [
"$$value",
{
"$and": [
{"$eq": ["$$this.tF", "N"]},
{
"$regexMatch": {
"input": "$$this.dateP",
"regex": "^2022"
}
}
]
}
]
}
}
},
{
"$reduce": {
"input": "$$this.s.d.t",
"initialValue": false,
"in": {
"$or": [
"$$value",
{
"$and": [
{"$eq": ["$$this.tF", "N"]},
{
"$regexMatch": {
"input": "$$this.dateP",
"regex": "^2022"
}
}
]
}
]
}
}
}
]
},
1,
0
]
}
]
}
}
}
]
}
}
}
}
},
{
"$group": {
"_id": null,
"totalCount": {"$sum": "$docCount"}
}
}
])
在mongoplayground.net上尝试它。
英文:
Here's one way you can do it without "$unwind", although the "$reduce" nesting levels seem very error prone. I hope you test this with lots of data before depending on it.
db.collection.aggregate([
{
"$match": {
"_a._p.s.d.t": {
"$elemMatch": {
"dateP": {"$regex": "^2022"},
"tF": "N"
}
},
"_a._p.s.c.t": {
"$elemMatch": {
"dateP": {"$regex": "^2022"},
"tF": "N"
}
}
}
},
{
"$project": {
"ap": "$_a._p"
}
},
{
"$project": {
"docCount": {
"$reduce": {
"input": "$ap",
"initialValue": 0,
"in": {
"$sum": [
"$$value",
{
"$reduce": {
"input": "$$this",
"initialValue": 0,
"in": {
"$sum": [
"$$value",
{
"$cond": [
{
"$and": [
{
"$reduce": {
"input": "$$this.s.c.t",
"initialValue": false,
"in": {
"$or": [
"$$value",
{
"$and": [
{"$eq": ["$$this.tF", "N"]},
{
"$regexMatch": {
"input": "$$this.dateP",
"regex": "^2022"
}
}
]
}
]
}
}
},
{
"$reduce": {
"input": "$$this.s.d.t",
"initialValue": false,
"in": {
"$or": [
"$$value",
{
"$and": [
{"$eq": ["$$this.tF", "N"]},
{
"$regexMatch": {
"input": "$$this.dateP",
"regex": "^2022"
}
}
]
}
]
}
}
}
]
},
1,
0
]
}
]
}
}
}
]
}
}
}
}
},
{
"$group": {
"_id": null,
"totalCount": {"$sum": "$docCount"}
}
}
])
Try it [mongoplayground.net](https://mongoplayground.net/p/L760LSbMdZU "Click me!").
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论