在尝试使用MongooseJs在Mongodb中按嵌套数组排序时，我遇到了一个小问题.
I've having a slight issue when trying to sort by a nested array in Mongodb using MongooseJs.
a)产品包含任务，每个任务都有子任务. b)任务有订单&每个子任务(task.order和task.subtask.order)也是如此.
a) A product contains tasks, each task has subTasks. b) The task has a order & so does each subTask (task.order & task.subtask.order).
以下是示例产品文档:
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 Aggregation管道订购任务&其中的子任务.这是我到目前为止的内容:
I'm using the Mongodb Aggregation pipeline to order the tasks & the subTasks within them. Here is what I have so far:
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" } ] } ]我真的很接近，所有订购似乎都可以正常进行.我只需要一些帮助，即可将subTask放回父级中.任何帮助都将不胜感激.
I'm really close, all the ordering seems to be working. I just need some help to put the subTasks back inside the parents. Any help greatly appreciated.
谢谢
推荐答案您从聚合管道的开始就犯了一个错误
You are making a mistake right from the beginning of your aggregation pipeline
$project: { "tasks" : 1 }，您将丢失所有数据.因此，首先您需要保留它:
by which you lose all your data. So first of all you need to reserve it doing:
$project: { tasks: 1, doc: { title: "$title", order: "$order", description: "$description", status: "$status" } }然后像在问题中一样执行您的$unwind:
Afterwards perform your $unwinds as you do in your question:
{$unwind: "$tasks"}, {$unwind: "$tasks.subTasks"}然后进行排序.您需要使用复合键进行排序，否则，按tasks.order排序后，按tasks.subTasks.order进行的排序将不成立.所以:
Then do the sorting. You need to do the sorting with compound keys, otherwise the ordering by tasks.subTasks.order will not hold as soon as you sort by tasks.order. So:
{$sort: {"tasks.order": -1, "tasks.subTasks.order": 1}}然后是困难的部分.您需要$group备份结果，第一步是$push备份subTasks，但是首先，再次需要保留任务属性:
And then comes the hard part. You need to $group back the results, and the first step is to $push back the subTasks, but first of all, again you need to preserve the task attributes:
$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" } }与tasks相同.请注意，在$group期间，您还需要向后投射task_doc属性:
And the same for the tasks. Pay attention that during the $grouping you also need to project back the task_doc attributes:
$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 }基本上就是这样.这是完整的原始聚合管道，因此您可以测试并查看是否获得期望的结果:
That is basically it. Here is the full raw aggregation pipeline, so you can test and see whether you get the desired result:
[ {$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 ion.
If an array field is empty or does not exist (is null) the $unwind operation on that field will return empty result. The solution to this situation is initially setting up the null/empty field to some zero value, e.g. "<empty-array>". Note, that you have to do this $projection for each array, before its $unwind.
看看此答案有关如何使用$ifNull运算符的信息.另外，请在此处签出.
Take a look at this answer about how to use the $ifNull operator. Also check out the $size operator here.
处理完此部分后，您需要$group返回结果，这可以使用 $cond运算符，以检查"<empty-array>"值
After dealing with this part, you need to $group back the results, and this can be achieved using the $cond operator, to check against the "<empty-array>" value
更多推荐
MongoDb管道聚合排序子子文档
发布评论