英文:
How to lookup in the same document under a collection in MongoDB
问题
我想要获取位于 "measures" 下的 "measureValues"，其中 "measureValues" 的 ID 与类别下的 "measureValues" 的 ID 匹配。我想要在类别下包括 "measureValue" 对象，而不仅仅是 ID。我应该如何做？我尝试编写聚合管道，但失败了。
我的输出应该如下所示：
{
"sections": [
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a8f"
},
"categories": [
{
"measureValues": [
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a95"
},
"measureValues": {
"nodeName": {
"64cbeb62b669cd29a5719a94": {
"nodeType": "nodeName",
"value": "measureValue",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
},
"comment": {
"64cbeb62b669cd29a5719a97": {
"nodeType": "comment",
"value": "this is a comment",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
}
},
"categoriesIds": [
"64cbeb62b669cd29a5719a91"
],
"measureName": "measure-1"
},
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a99"
},
"measureValues": {
"nodeName": {
"64cbeb62b669cd29a5719a98": {
"nodeType": "nodeName",
"value": "measureValue",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
},
"date": {
"64cbeb62b669cd29a5719a9b": {
"nodeType": "date",
"value": "2023-02-02",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
}
},
"categoriesIds": [
"64cbeb62b669cd29a5719a91"
],
"measureName": "measure-1"
}
]
}
]
}
],
"_class": "com.myapp.cqrsdemo.model.ProjectGroup"
}
英文:
I am having below document in MongoDB [UPDATE : Shortened the document]
{
"sections": [
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a8f"
},
"categories": [
{
"measureValues": [
"64cbeb62b669cd29a5719a95",
"64cbeb62b669cd29a5719a99"
]
}
]
}
],
"measures": [
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a93"
},
"name": {
"64cbeb62b669cd29a5719a92": {
"nodeType": "nodeName",
"value": "measure-1"
}
},
"measureValues": [
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a95"
},
"measureValues": {
"nodeName": {
"64cbeb62b669cd29a5719a94": {
"nodeType": "nodeName",
"value": "measureValue",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
},
"comment": {
"64cbeb62b669cd29a5719a97": {
"nodeType": "comment",
"value": "this is a comment",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
}
},
"categoriesIds": [
"64cbeb62b669cd29a5719a91"
],
"measureName": "measure-1"
},
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a99"
},
"measureValues": {
"nodeName": {
"64cbeb62b669cd29a5719a98": {
"nodeType": "nodeName",
"value": "measureValue",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
},
"date": {
"64cbeb62b669cd29a5719a9b": {
"nodeType": "date",
"value": "2023-02-02",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
}
},
"categoriesIds": [
"64cbeb62b669cd29a5719a91"
],
"measureName": "measure-1"
}
]
}
],
"_class": "com.myapp.cqrsdemo.model.ProjectGroup"
}
I want to fetch the "measureValues" under "measures" where the id of the "measureValues" is matching with the id of the measureValues under categories. I want to include the measureValue object under categories instead of just the Ids.
How can I do that? I tried writing aggregate pipeline, but it failed.
My Output should look like below.
{
"sections": [
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a8f"
},
"categories": [
{
"measureValues": [
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a95"
},
"measureValues": {
"nodeName": {
"64cbeb62b669cd29a5719a94": {
"nodeType": "nodeName",
"value": "measureValue",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
},
"comment": {
"64cbeb62b669cd29a5719a97": {
"nodeType": "comment",
"value": "this is a comment",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
}
},
"categoriesIds": [
"64cbeb62b669cd29a5719a91"
],
"measureName": "measure-1"
},
{
"_id": {
"$oid": "64cbeb62b669cd29a5719a99"
},
"measureValues": {
"nodeName": {
"64cbeb62b669cd29a5719a98": {
"nodeType": "nodeName",
"value": "measureValue",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
},
"date": {
"64cbeb62b669cd29a5719a9b": {
"nodeType": "date",
"value": "2023-02-02",
"_class": "com.myapp.cqrsdemo.model.DataObj"
}
}
},
"categoriesIds": [
"64cbeb62b669cd29a5719a91"
],
"measureName": "measure-1"
}
]
}
]
}
],
"_class": "com.myapp.cqrsdemo.model.ProjectGroup"
}
答案1
得分: 1
以下是翻译好的部分：
一个选项是：
1. 创建一个名为 `measureValuesArr` 的变量，并在其中存储所有 `measures` 下的所有 `measureValues` 的单个扁平数组。
2. 由于 `sections.categories.measureValues` 是一个双重嵌套数组（在数组的数组中），使用双重 `$map` 来访问其中的所有实例。
3. 使用 `$arrayElemAt` 与 `$indexOfArray` 来找到在 `measureValuesArr` 中匹配的 `measureValues` 项
希望这对你有所帮助。如果有任何其他问题，请随时提出。
英文:
One option is:
- Create a var
measureValuesArrand store inside it a single flatten array of allmeasureValuesunder allmeasures - Since the
sections.categories.measureValuesis a double nested array (inside an array on arrays) use double$mapto get into all instances of it. - Use
$arrayElemAtwith$indexOfArrayto find the matchingmeasureValuesitem insidemeasureValuesArr
db.collection.aggregate([
{$set: {
sections: {$let: {
vars: {measureValuesArr: {$reduce: {
input: "$measures",
initialValue: [],
in: {$concatArrays: ["$$value", "$$this.measureValues"]}
}}},
in: {$map: {
input: "$sections",
as: "s",
in: {$mergeObjects: [
"$$s",
{categories: {$map: {
input: "$$s.categories",
as: "c",
in: {$mergeObjects: [
"$$c",
{measureValues: {$map: {
input: "$$c.measureValues",
as: "m",
in: {$arrayElemAt: [
"$$measureValuesArr",
{$indexOfArray: [
"$$measureValuesArr._id",
{$toObjectId: "$$m"}
]}
]}
}}}
]}
}}}
]}
}}
}}
}}
])
See how it works on the playground example
*This solution assumes that a matching object does exists somewhere under measures if there is an option that it does not exists, an handling of this case should be added to the query.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论