MongoDb管道聚合排序子文档

Question

当我试图使用MongooseJs在Mongodb中通过嵌套数组进行排序时，遇到了一个小问题。

a) 产品包含任务，每个任务都有子任务
b)任务有一个订单&每个子任务(task.order&task.subTask.orde)也是如此

这是一个示例产品文档：

db.products.find({_id: ObjectId("554a13d4b692088a38f01f3b")})

结果：

{
"_id" : ObjectId("554a13d4b692088a38f01f3b"),
"title" : "product title",
"order" : 3,
"description" : "Description here ",
"status" : "live",
"tasks" : [ 
{
"title" : "task 1",
"description" : "task 1 desc",
"order" : 10,
"_id" : ObjectId("554a13d4b692088a38f01f3a"),
"status" : "live",
"subTasks" : [ 
{
"title" : "task 1 sub 1",
"content" : "aaa",
"order" : -2,
"_id" : ObjectId("554a13d4b692088a38f01f5a"),
"status" : "live"
}, 
{
"title" : "task 1 sub 2",
"content" : "aaa",
"order" : 1,
"_id" : ObjectId("554a13d4b692088a38f01f3a"),
"status" : "live"
}, 
{
"title" : "task 1 sub 4",
"content" : "aaa",
"order" : 8,
"_id" : ObjectId("554a13d4b692088a38f01f4a"),
"status" : "live"
}, 
{
"title" : "task 1 sub 3 ",
"content" : "aaa",
"order" : 2,
"_id" : ObjectId("5550d0a61662211332d9a973"),
"status" : "live"
}
]
}, 
{
"title" : "task 2",
"description" : "task desc 2",
"order" : 1,
"_id" : ObjectId("5550855f9ee2db4e3958d299"),
"status" : "live",
"subTasks" : [ 
{
"title" : "task 2 sub 1",
"content" : "bbb",
"order" : 1,
"_id" : ObjectId("55508f459ee2db4e3958d29a"),
"status" : "live"
}
]
}, 
{
"title" : "task 3",
"description" : "task 3 desc",
"order" : 2,
"_id" : ObjectId("5551b844bb343a620f85f323"),
"status" : "live",
"subTasks" : [ 
{
"title" : "task 3 sub 2",
"content" : "cccc",
"order" : 0,
"_id" : ObjectId("5551b88abb343a620f85f324"),
"status" : "live"
}, 
{
"title" : "task 3 sub 4",
"content" : "cccc",
"order" : 1,
"_id" : ObjectId("5551b8f1bb343a620f85f325"),
"status" : "hidden"
}, 
{
"title" : "task 3 sub 3",
"content" : "ccc",
"order" : 2,
"_id" : ObjectId("5551ba40bb343a620f85f327"),
"status" : "hidden"
}, 
{
"title" : "task 3 sub 1",
"content" : "cccc",
"order" : -1,
"_id" : ObjectId("5551bcb8c31283c051d30b7c"),
"status" : "hidden"
}
]
}
]

}

我正在使用Mongodb聚合管道来订购任务&其中的子任务。以下是我目前所拥有的：

db.products.aggregate([
{
$project: {
"tasks" : 1
}
},
{
$match: {
_id: ObjectId("554a13d4b692088a38f01f3b")
}
},
{
$unwind: "$tasks"
},
{
$project: {
"tasks": 1,
"subTasks": 1
}
},
{
$unwind: "$tasks.subTasks"
},
{
$sort: {
"tasks.subTasks.order": 1
}
},
{
$sort: {
"tasks.order": 1
}
}
])

结果：

{
"result": [
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 2",
"description": "task desc 2",
"order": 1,
"_id": ObjectId("5550855f9ee2db4e3958d299"),
"status": "live",
"subTasks": {
"title": "task 2 sub 1",
"content": "bbb",
"order": 1,
"_id": ObjectId("55508f459ee2db4e3958d29a"),
"status": "live"
}
}
},
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 3",
"description": "task 3 desc",
"order": 2,
"_id": ObjectId("5551b844bb343a620f85f323"),
"status": "live",
"subTasks": {
"title": "task 3 sub 1",
"content": "cccc",
"order": -1,
"_id": ObjectId("5551bcb8c31283c051d30b7c"),
"status": "hidden"
}
}
},
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 3",
"description": "task 3 desc",
"order": 2,
"_id": ObjectId("5551b844bb343a620f85f323"),
"status": "live",
"subTasks": {
"title": "task 3 sub 2",
"content": "cccc",
"order": 0,
"_id": ObjectId("5551b88abb343a620f85f324"),
"status": "live"
}
}
},
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 3",
"description": "task 3 desc",
"order": 2,
"_id": ObjectId("5551b844bb343a620f85f323"),
"status": "live",
"subTasks": {
"title": "task 3 sub 4",
"content": "cccc",
"order": 1,
"_id": ObjectId("5551b8f1bb343a620f85f325"),
"status": "hidden"
}
}
},
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 3",
"description": "task 3 desc",
"order": 2,
"_id": ObjectId("5551b844bb343a620f85f323"),
"status": "live",
"subTasks": {
"title": "task 3 sub 3",
"content": "ccc",
"order": 2,
"_id": ObjectId("5551ba40bb343a620f85f327"),
"status": "hidden"
}
}
},
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 1",
"description": "task 1 desc",
"order": 10,
"_id": ObjectId("554a13d4b692088a38f01f3a"),
"status": "live",
"subTasks": {
"title": "task 1 sub 1",
"content": "aaa",
"order": -2,
"_id": ObjectId("554a13d4b692088a38f01f5a"),
"status": "live"
}
}
},
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 1",
"description": "task 1 desc",
"order": 10,
"_id": ObjectId("554a13d4b692088a38f01f3a"),
"status": "live",
"subTasks": {
"title": "task 1 sub 2",
"content": "aaa",
"order": 1,
"_id": ObjectId("554a13d4b692088a38f01f3a"),
"status": "live"
}
}
},
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 1",
"description": "task 1 desc",
"order": 10,
"_id": ObjectId("554a13d4b692088a38f01f3a"),
"status": "live",
"subTasks": {
"title": "task 1 sub 3 ",
"content": "aaa",
"order": 2,
"_id": ObjectId("5550d0a61662211332d9a973"),
"status": "live"
}
}
},
{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"tasks": {
"title": "task 1",
"description": "task 1 desc",
"order": 10,
"_id": ObjectId("554a13d4b692088a38f01f3a"),
"status": "live",
"subTasks": {
"title": "task 1 sub 4",
"content": "aaa",
"order": 8,
"_id": ObjectId("554a13d4b692088a38f01f4a"),
"status": "live"
}
}
}
],
"ok": 1

}

预期结果：

{
"_id": ObjectId("554a13d4b692088a38f01f3b"),
"title": "product title",
"order": 3,
"description": "Description here ",
"status": "live",
"tasks": [
{
"title": "task 2",
"description": "task desc 2",
"order": 1,
"_id": ObjectId("5550855f9ee2db4e3958d299"),
"status": "live",
"subTasks": [
{
"title": "task 2 sub 1",
"content": "bbb",
"order": 1,
"_id": ObjectId("55508f459ee2db4e3958d29a"),
"status": "live"
}
]
},
{
"title": "task 3",
"description": "task 3 desc",
"order": 2,
"_id": ObjectId("5551b844bb343a620f85f323"),
"status": "live",
"subTasks": [
{
"title": "task 3 sub 1",
"content": "cccc",
"order": -1,
"_id": ObjectId("5551bcb8c31283c051d30b7c"),
"status": "hidden"
},
{
"title": "task 3 sub 2",
"content": "cccc",
"order": 0,
"_id": ObjectId("5551b88abb343a620f85f324"),
"status": "live"
},
{
"title": "task 3 sub 3",
"content": "ccc",
"order": 2,
"_id": ObjectId("5551ba40bb343a620f85f327"),
"status": "hidden"
}{
"title": "task 3 sub 4",
"content": "cccc",
"order": 1,
"_id": ObjectId("5551b8f1bb343a620f85f325"),
"status": "hidden"
}
]
}{
"title": "task 1",
"description": "task 1 desc",
"order": 10,
"_id": ObjectId("554a13d4b692088a38f01f3a"),
"status": "live",
"subTasks": [
{
"title": "task 1 sub 1",
"content": "aaa",
"order": -2,
"_id": ObjectId("554a13d4b692088a38f01f5a"),
"status": "live"
},
{
"title": "task 1 sub 2",
"content": "aaa",
"order": 1,
"_id": ObjectId("554a13d4b692088a38f01f3a"),
"status": "live"
},
{
"title": "task 1 sub 3 ",
"content": "aaa",
"order": 2,
"_id": ObjectId("5550d0a61662211332d9a973"),
"status": "live"
},
{
"title": "task 1 sub 4",
"content": "aaa",
"order": 8,
"_id": ObjectId("554a13d4b692088a38f01f4a"),
"status": "live"
}
]
}
]

我真的很接近，所有的订单似乎都在起作用。我只需要一些帮助，把子任务放回父母体内。非常感谢您的帮助。

感谢

Answer 1

您从聚合管道的一开始就犯了一个错误

$project: {
"tasks" : 1
}

由此您将丢失所有数据。因此，首先你需要保留它做：

$project: {
tasks: 1,
doc: {
title: "$title", 
order: "$order", 
description: "$description", 
status: "$status"
}
}

之后，按照您在问题中所做的那样执行$unwind：

{$unwind: "$tasks"}, {$unwind: "$tasks.subTasks"}

然后进行排序。您需要使用复合键进行排序，否则按tasks.subTasks.order排序将不会在按tasks.order排序后立即保持。因此：

{$sort: {"tasks.order": -1, "tasks.subTasks.order": 1}}

然后是困难的部分。您需要$group返回结果，第一步是$push返回subTasks，但首先，您需要保留任务属性：

$project: {
doc: 1, 
task_id: "$tasks._id", 
tasks_doc: {
title: "$tasks.title", 
description: "$tasks.description", 
order: "$tasks.order", 
status: "$tasks.status"
}, 
subTasks: "$tasks.subTasks"
}

收集subTasks:

$group: {
_id: {
_id: "$_id", 
task_id: "$task_id", 
doc: "$doc", 
task_doc: "$tasks_doc"
}, 
subTasks: {
$push: "$subTasks"
}
}

CCD_ 8也是如此。请注意，在$group过程中，您还需要投影回task_doc属性：

$group: {
_id: {
_id: "$_id._id", 
doc: "$_id.doc"
}, 
tasks: {
$push: {
_id: "$_id.task_id", 
title: "$_id.task_doc.title", 
description: "$_id.task_doc.description",
order: "$_id.task_doc.order", 
status: "$_id.task_doc.status" 
subTasks: "$subTasks"
}
}
}

然后投影回根doc属性：

$project: {
_id: "$_id._id", 
title: "$_id.doc.title", 
description: "$_id.doc.description", 
order: "$_id.doc.order", 
status: "$_id.doc.status", 
tasks: 1
}

基本上就是这样。这是完整的原始聚合管道，所以你可以测试并看看你是否得到了想要的结果：

[
{$match: {_id: ObjectId("554a13d4b692088a38f01f3b")}}, 
{$project: {tasks: 1, doc: {title: "$title", order: "$order", description: "$description", status: "$status"}}}, 
{$unwind: "$tasks"}, 
{$unwind: "$tasks.subTasks"}, 
{$sort: {"tasks.order": -1, "tasks.subTasks.order": 1}}, 
{$project: {doc: 1, task_id: "$tasks._id", tasks_doc: {title: "$tasks.title", description: "$tasks.description", order: "$tasks.order", status: "$tasks.status"}, subTasks: "$tasks.subTasks"}}, 
{$group: {_id: {_id: "$_id", task_id: "$task_id", doc: "$doc", task_doc: "$tasks_doc"}, subTasks: {$push: "$subTasks"}}}, 
{$group: {_id: {_id: "$_id._id", doc: "$_id.doc"}, tasks: {$push: {_id: "$_id.task_id", title: "$_id.task_doc.title", description: "$_id.task_doc.description", order: "$_id.task_doc.order", status: "$_id.task_doc.status", subTasks: "$subTasks"}}}}, 
{$project: {_id: "$_id._id", title: "$_id.doc.title", description: "$_id.doc.description", order: "$_id.doc.order", status: "$_id.doc.status", tasks: 1}}
]

更新

如果数组字段为空或不存在(为null)，则对该字段的$unwind操作将返回空结果。这种情况的解决方案最初是将null/空字段设置为某个zero值，例如"<empty-array>"。请注意，您必须为每个阵列在其$unwind之前执行此$project离子。

看看这个关于如何使用$ifNull运算符的答案。也可以在此处查看$size运算符。

处理完这一部分后，您需要$group返回结果，这可以使用$cond运算符来检查"<empty-array>"值