优化MongoDB聚合查询
优化MongoDB聚合查询
提问于 2018-01-23 16:48:26
我对Mongo非常陌生，我能够创建一个提供我所需输出的查询。但是它出现的时间很长(似乎是一个简单的查询)。
关于如何优化它的技巧将是欢迎的。
总之，我有5个项，查询应该获得上一次修改的项目的信息，以及该项的最后修改的项目的信息，以及具有的其他项目的名称。它还需要检索所有其他项的名称和id，。
这是一个项目的示例(都类似于以下内容)：
{
"_id" : ObjectId("5a16ebc6871fbc64c0e39e43"),
"date_modified" : ISODate("2017-12-15T07:18:04.774Z"),
"name" : "TEST COMPANY 1",
"projects" : [
{
"project" : {
"name" : "TEST PROJECT 1",
"date_modified" : ISODate("2014-12-15T07:18:04.774Z"),
"description" : "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nulla quam velit, vulputate eu pharetra nec, mattis ac neque. Duis vulputate commodo lectus, ac blandit elit tincidunt id. Sed rhoncus, tortor sed eleifend tristique, tortor mauris molestie elit, et lacinia ipsum quam nec dui. Quisque nec mauris sit amet elit iaculis pretium sit amet quis magna. Aenean velit odio, elementum in tempus ut, vehicula eu diam. Pellentesque rhoncus aliquam mattis. Ut vulputate eros sed felis sodales nec vulputate justo hendrerit. Vivamus varius pretium ligula, a aliquam odio euismod sit amet. Quisque laoreet sem sit amet orci ullamcorper at ultricies metus viverra. Pellentesque arcu mauris, malesuada quis ornare accumsan, blandit sed diam.",
"url" : "test_project_1",
"task_groups" : [
{
"task_group" : {
"id" : "1",
"folders" : [
{
"folder" : {
"name" : "test folder 1",
"tasks" : [
{
"task" : {
"name" : "test task 1",
"versions" : [
{
"version" : {
"id" : "1"
}
}
]
}
}
]
}
}
],
"tasks" : [
{
"task" : {
"name" : "test task 1",
"versions" : [
{
"version" : {
"id" : "1"
}
}
]
}
}
]
}
}
]
}
},
{
"project" : {
"name" : "TEST PROJECT 2",
"date_modified" : ISODate("2017-12-15T07:18:04.774Z"),
"description" : "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nulla quam velit, vulputate eu pharetra nec, mattis ac neque. Duis vulputate commodo lectus, ac blandit elit tincidunt id. Sed rhoncus, tortor sed eleifend tristique, tortor mauris molestie elit, et lacinia ipsum quam nec dui. Quisque nec mauris sit amet elit iaculis pretium sit amet quis magna. Aenean velit odio, elementum in tempus ut, vehicula eu diam. Pellentesque rhoncus aliquam mattis. Ut vulputate eros sed felis sodales nec vulputate justo hendrerit. Vivamus varius pretium ligula, a aliquam odio euismod sit amet. Quisque laoreet sem sit amet orci ullamcorper at ultricies metus viverra. Pellentesque arcu mauris, malesuada quis ornare accumsan, blandit sed diam.",
"url" : "test_project_1",
"task_groups" : [
{
"task_group" : {
"id" : "1",
"folders" : [
{
"folder" : {
"name" : "test folder 1",
"tasks" : [
{
"task" : {
"name" : "test task 1",
"versions" : [
{
"version" : {
"id" : "1"
}
}
]
}
}
]
}
}
],
"tasks" : [
{
"task" : {
"name" : "test task 1",
"versions" : [
{
"version" : {
"id" : "1"
}
}
]
}
}
]
}
}
]
}
}
]}
这是我的查询，就像我说的那样，它可以完成这项工作，但我不知道我是否很好地使用了Mongo的聚合选项：
const searchQuery = collection.aggregate([
{
"$sort": {
"date_modified": -1,
}
},
{
"$group": {
"_id": 0,
"firstPipe": { "$first": { "name": "$name", "id": "$_id", "date_modified": "$date_modified", "projects": "$projects" } },
"otherCompanies": { "$push": { "name": "$name", "id": "$_id" } },
}
},
{ $unwind: "$firstPipe.projects" },
{
"$sort": {
"firstPipe.projects.project.date_modified": -1
}
},
{
"$group": {
"_id": 0,
"firstProject": { "$first": "$firstPipe.projects" },
"projectNames": { "$push": { "project": { "name": "$firstPipe.projects.project.name" } } },
"secondPipe": { "$first": { "name": "$firstPipe.name", "id": "$firstPipe.id", "date_modified": "$firstPipe.date_modified" } },
"otherCompanies": { "$first": "$otherCompanies" }
}
},
{
"$project": {
"projects": { "$concatArrays": [["$firstProject"], { "$slice": ["$projectNames", 1, { "$size": "$projectNames" }] }] },
"secondPipe": "$secondPipe",
"otherCompanies": "$otherCompanies"
}
},
{
"$group": {
"_id": 0,
"thirdPipe": { "$push": { "name": "$secondPipe.name", "id": "$secondPipe.id", "date_modified": "$secondPipe.date_modified", "projects": "$projects" } },
"otherCompanies": { "$first": "$otherCompanies" }
}
},
{
"$project": {
"data": { "$concatArrays": ["$thirdPipe", { "$slice": ["$otherCompanies", 2, { "$size": "$otherCompanies" }] }] }
}
},
{ "$unwind": "$data" },
{ "$replaceRoot": { "newRoot": "$data" } },
]);这是我得到并需要的输出：
[
{
"name": "TEST COMPANY 1",
"id": "5a16ebc6871fbc64c0e39e43",
"date_modified": "2017-12-15T07:18:04.774Z",
"projects": [
{
"project": {
"name": "TEST PROJECT 2",
"date_modified": "2017-12-15T07:18:04.774Z",
"description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nulla quam velit, vulputate eu pharetra nec, mattis ac neque. Duis vulputate commodo lectus, ac blandit elit tincidunt id. Sed rhoncus, tortor sed eleifend tristique, tortor mauris molestie elit, et lacinia ipsum quam nec dui. Quisque nec mauris sit amet elit iaculis pretium sit amet quis magna. Aenean velit odio, elementum in tempus ut, vehicula eu diam. Pellentesque rhoncus aliquam mattis. Ut vulputate eros sed felis sodales nec vulputate justo hendrerit. Vivamus varius pretium ligula, a aliquam odio euismod sit amet. Quisque laoreet sem sit amet orci ullamcorper at ultricies metus viverra. Pellentesque arcu mauris, malesuada quis ornare accumsan, blandit sed diam.",
"url": "test_project_1",
"task_groups": [
{
"task_group": {
"id": "1",
"folders": [
{
"folder": {
"name": "test folder 1",
"tasks": [
{
"task": {
"name": "test task 1",
"versions": [
{
"version": {
"id": "1"
}
}
]
}
}
]
}
}
],
"tasks": [
{
"task": {
"name": "test task 1",
"versions": [
{
"version": {
"id": "1"
}
}
]
}
}
]
}
}
]
}
},
{
"project": {
"name": "TEST PROJECT 1"
}
}
]
},
{
"name": "TEST COMPANY 4",
"id": "5a16ebc6871f1c64c0e39e41"
},
{
"name": "TEST COMPANY 3",
"id": "5a16ebc6871fbc64c0e39e11"
},
{
"name": "TEST COMPANY 2",
"id": "5a16ebc6871fbc64c0e39e41"
}
]回答 1
Stack Overflow用户
回答已采纳
发布于 2018-01-24 00:27:50
您可以通过移动逻辑来分离项目来稍微简化一些。
两次变化
$group现在负责拆分项目，方法是在$max修改日期与$map中的$cond匹配时，在$map中找到修改后的日期以返回整个项目文档。
$addFields阶段，将项目嵌入到主文档中。
[
{"$sort":{"date_modified":-1}},
{"$group":{
"_id":0,
"topdata":{"$first":{"name":"$name","id":"$_id","date_modified":"$date_modified"}},
"projects":{
"$first":{
"$let":{
"vars":{"ldm":{"$max":"$projects.project.date_modified"}},
"in":{
"$map":{
"input":"$projects",
"as":"project",
"in":{
"$cond":{
"if":{"$eq":["$$project.project.date_modified","$$ldm"]},
"then":"$$project",
"else":{"project":{"name":"$$project.project.name"}}
}
}
}
}
}
}
},
"otherCompanies":{"$push":{"name":"$name","id":"$_id"}}
}},
{"$unwind":"$projects"},
{"$sort":{"projects.project.date_modified":-1}},
{"$group":{
"_id":0,
"topdata":{"$first":"$topdata"},
"projects":{"$push":"$projects"},
"otherCompanies":{"$first":"$otherCompanies"}
}},
{"$addFields":{"topdata.projects":"$projects"}},
{"$project":{"data":{"$concatArrays":[["$topdata"],{"$slice":["$otherCompanies",1,{"$size":"$otherCompanies"}]}]}}},
{"$unwind":"$data"},
{"$replaceRoot":{"newRoot":"$data"}}
]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48406912
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号