Sorting nested Array in MongoDB using aggregate

Question

I am trying to sort the nested array "subCategoryList on top of sorting by _id on the parent document. Newbie to mongo. Any help will be appreciated

Here is sample documents:

[
    {
        "_id": 1000,
        "name": "Automobiles",
        "parentId": "",
        "helpText": "Year, Brand, Model, Color, Size"
    },
    {
        "_id": 1004,
        "name": "RV / Campers",
        "parentId": 1000,
        "helpText": ""
    },
    {
        "_id": 1001,
        "name": "Car / SUV / Truck",
        "parentId": 1000,
        "helpText": ""
    }
]

Here is what I tried:

db.Category.aggregate([
    {
        "$match": {
            "parentId": ""
        }
    },
    {
        "$lookup": {
            "from": "Category", "localField": "_id", "foreignField": "parentId", "as": "subCategoryList"
        }
    },
    {
        $sort: {
            _id: 1
        }
    }
]).pretty()

But I get:

    {
    "_id" : 17000,
    "name" : "Music",
    "parentId" : "",
    "helpText" : "Help Text - Brand, Model, Title",
    "subCategoryList" : [
        {
            "_id" : 17001,
            "name" : "DVD / Blu-ray",
            "parentId" : 17000,
            "helpText" : ""
        },
        {
            "_id" : 17002,
            "name" : "Player",
            "parentId" : 17000,
            "helpText" : ""
        }
    ]
}
{
    "_id" : 20000,
    "name" : "Sports Gear",
    "parentId" : "",
    "helpText" : "Help Text - Brand, Model, Gear Type, Size, Color,",
    "subCategoryList" : [
        {
            "_id" : 20002,
            "name" : "Football",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20007,
            "name" : "Tennis",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20008,
            "name" : "Cricket",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20004,
            "name" : "Hockey",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20003,
            "name" : "Golf",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20006,
            "name" : "Basketball",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20005,
            "name" : "Soccer",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20010,
            "name" : "Camping / Hiking",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20009,
            "name" : "Cycling",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20001,
            "name" : "Baseball",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20012,
            "name" : "Skiing",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20011,
            "name" : "Swimming",
            "parentId" : 20000,
            "helpText" : ""
        },
        {
            "_id" : 20099,
            "name" : "Other",
            "parentId" : 20000,
            "helpText" : ""
        }
    ]
}

Sree Durkesh · Accepted Answer · 2020-07-18 12:16:56Z

1

db.hardwares.aggregate([
    {$unwind: "$subCategoryList"},
    {$sort: {"subCategoryList._id": 1}},
    {$group: {_id:"$_id", subCategoryList: {$push:"$subCategoryList"}}}
]);

It could be helps you, try like this... May be it didn't work but it gives an idea

edited Jul 18, 2020 at 12:16

answered Jul 18, 2020 at 8:41

Sree Durkesh

418 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Slabbaert · Accepted Answer · 2020-07-18 13:50:35Z

Mongo doesn't have a built in function to sort an inner array, However Mongo v4.4 introduces the $function aggregation operator. this allows us to use custom javascript functions within a pipeline.

You could use it like so:

db.Category.aggregate([
    {
        $addFields: {
            "subCategoryList":
                {
                    $function: {
                        body: function (categories) {
                            return categories.sort((a, b) => a._id - b._id);
                        },
                        args: ["$subCategoryList"],
                        lang: "js"
                    }
                }
        }
    }
])

For lesser Mongo versions you will have to first $unwind the array. then $sort it and finally constructing the original structure or in your case because you are $lookuping the inner array you could use the other $lookup syntax introduced at v3.6 to $sort within the $lookup:

db.Category.aggregate([
    {
        "$match": {
            "parentId": ""
        }
    },
    {
        "$lookup": {
            "from": "Category",
            let: {id: "$_id"},
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: ["$$id", "$parentId"]
                        }
                    }
                },
                {
                    $sort: {
                        _id: 1
                    }
                }
            ],
            "as": "subCategoryList"
        }
    },
    {
        $sort: {
            _id: 1
        }
    }
]);

Collectives™ on Stack Overflow

Sorting nested Array in MongoDB using aggregate

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related