CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Sharry
Sharry

Reputation: 150

MongoDb Pipeline Aggregation sorting sub sub documents

I've having a slight issue when trying to sort by a nested array in Mongodb using MongooseJs.

a) A product contains tasks, each task has subTasks.
b) The task has a order & so does each subTask (task.order & task.subtask.order).

Here is a sample product document: 

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

Result:

{
"_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"
            }
        ]
    }
]

}

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
        }
    }
])

Results:

{
"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

}

Expected result:

{
"_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"
            }
        ]
    }
]

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.

Thanks

Upvotes: 3

Views: 1170

Answers (1)

bagrat
bagrat

Reputation: 7458

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"
                  }
          }

Afterwards perform your $unwinds as you do in your question:

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

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}}

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"
          }

...collect subTasks:

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

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"
                            }
                  }
        }

And then project back the root doc attributes:

$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}}
]

UPDATE

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.

Take a look at this answer about how to use the $ifNull operator. Also check out the $size operator here.

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

Upvotes: 5

Related Questions