How to sort inner array elements while maintaining different sorting for outer documents?

Question

I have a collection of documents each of which has an embedded array. I need the elements of each array to be sorted within themselves, while the containing documents should remain ordered as they are.

As an exemplification, taking the data from this similar question on sorting:

[
    {
        name: 'item1',
        slots: [
            { date : ISODate("2013-01-18T23:00:00Z") },
            { date : ISODate("2013-02-05T23:00:00Z") },
            { date : ISODate("2013-03-24T23:00:00Z") },
        ]
    },
    {
        name: 'item2',
        slots: [
            { date : ISODate("2013-01-12T23:00:00Z") },
            { date : ISODate("2013-01-03T23:00:00Z") },
            { date : ISODate("2013-03-04T23:00:00Z") },
        ]
    },
    {
        name: 'item3',
        slots: [
            { date : ISODate("2013-03-14T23:00:00Z") },
            { date : ISODate("2013-02-18T23:00:00Z") },
            { date : ISODate("2013-03-07T23:00:00Z") },
        ]
    }
]

My expected result is slightly different:

[
    {
        name: 'item3',
        slots: [
            { date : ISODate("2013-02-18T23:00:00Z") },
            { date : ISODate("2013-03-07T23:00:00Z") },
            { date : ISODate("2013-03-14T23:00:00Z") },
        ]
    },
    {
        name: 'item2',
        slots: [
            { date : ISODate("2013-01-03T23:00:00Z") },
            { date : ISODate("2013-01-12T23:00:00Z") },
            { date : ISODate("2013-03-04T23:00:00Z") },
        ]
    },
    {
        name: 'item1',
        slots: [
            { date : ISODate("2013-01-18T23:00:00Z") },
            { date : ISODate("2013-02-05T23:00:00Z") },
            { date : ISODate("2013-03-24T23:00:00Z") },
        ]
    }
]

How can I achieve this in MongoDB 3.6? I'd like to use the aggregation pipeline, if possible.

I didn't try much so far, since the only stage I can think would fit for this is the $sort, but as stated in the documentation, it will sort the outer collection.

score 2 · Accepted Answer · answered Jan 30 '18 at 15:49

You can use the below aggregation query.

$unwind the slots array followed by sorting the docs date asc and $group back on _id to get the sorted array.

$sort on name desc to sort the documents.

db.col.aggregate([
  {"$unwind":"$slots"},
  {"$sort":{"slots.date":1}},
  {"$group":{"_id":"$_id","name":{"$first":"$name"},"slots":{"$push":"$slots"}}},
  {"$sort":{"name":-1}},
])

How to sort inner array elements while maintaining different sorting for outer documents?

1 Answers1