Mongodb sort inner array

Question

I've been looking for a while now and can't seem to sort an inner array and keep that in the doc that I'm currently working with.

{
    "service": {
        "apps": {
            "updates": [
              {
                "n" : 1
                "date": ISODate("2012-03-10T16:15:00Z")
              },
              {
                "n" : 2
                "date": ISODate("2012-01-10T16:15:00Z")
              },
              {
                "n" : 5
                "date": ISODate("2012-07-10T16:15:00Z")
              }
            ]
        }
     }
 }

So I want to keep the item to be returned as the service, but have my updates array sorted. So far with the shell I have:

db.servers.aggregate(
        {$unwind:'$service'},
        {$project:{'service.apps':1}},
        {$unwind:'$service.apps'}, 
        {$project: {'service.apps.updates':1}}, 
        {$sort:{'service.apps.updates.date':1}});

Anyone think they can help on this?

Does this answer your question? Sort nested array of objects — nimrod serok
– nimrod serok, Commented May 30, 2022 at 16:09

JohnnyHK · Accepted Answer · 2013-03-13 15:09:43Z

71

You can do this by $unwinding the updates array, sorting the resulting docs by date, and then $grouping them back together on _id using the sorted order.

db.servers.aggregate(
    {$unwind: '$service.apps.updates'}, 
    {$sort: {'service.apps.updates.date': 1}}, 
    {$group: {_id: '$_id', 'updates': {$push: '$service.apps.updates'}}}, 
    {$project: {'service.apps.updates': '$updates'}})

answered Mar 13, 2013 at 15:09

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

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

7 Comments

Ricky Hartmann Over a year ago

Awesome! I'm pretty new to aggregation and had a feeling it could do something like this.

Ricky Hartmann Over a year ago

what about if apps had a field like "name" and I wanted to keep the name in the result set as well?

JohnnyHK Over a year ago

@user1251624 You would include that field in the $group (in the _id or as a separate field) and $project. If you need more help on that it's probably best to ask that as a separate question as it can be non-trivial.

RaR Over a year ago

@JohnnyHK Is the order of grouping after sort always guaranteed?

Ingo Bürk Over a year ago

@RaR For $push yes, AFAIK. For $addToSet no, unfortunately.

|

Xavier Guihot · Accepted Answer · 2020-03-31 18:23:23Z

Starting in Mongo 4.4, the $function aggregation operator allows applying a custom javascript function to implement behaviour not supported by the MongoDB Query Language.

For instance, in order to sort an array of objects by one of their fields:

// {
//   "service" : { "apps" : { "updates" : [
//     { "n" : 1, "date" : ISODate("2012-03-10T16:15:00Z") },
//     { "n" : 2, "date" : ISODate("2012-01-10T16:15:00Z") },
//     { "n" : 5, "date" : ISODate("2012-07-10T16:15:00Z") }
//   ]}}
// }
db.collection.aggregate(
  { $set: {
    { "service.apps.updates":
      { $function: {
          body: function(updates) {
            updates.sort((a, b) => a.date - b.date);
            return updates;
          },
          args: ["$service.apps.updates"],
          lang: "js"
      }}
    }
  }
)
// {
//   "service" : { "apps" : { "updates" : [
//     { "n" : 2, "date" : ISODate("2012-01-10T16:15:00Z") },
//     { "n" : 1, "date" : ISODate("2012-03-10T16:15:00Z") },
//     { "n" : 5, "date" : ISODate("2012-07-10T16:15:00Z") }
//   ]}}
// }

This modifies the array in place, without having to apply a combination of expensive $unwind, $sort and $group stages.

$function takes 3 parameters:

body, which is the function to apply, whose parameter is the array to modify.
args, which contains the fields from the record that the body function takes as parameter. In our case "$service.apps.updates".
lang, which is the language in which the body function is written. Only js is currently available.

For Atlas users, $function is not available in free tier (as of May 2021)

Xavier Guihot · Accepted Answer · 2022-03-14 16:08:00Z

Starting in Mongo 5.2, it's the exact use case for the new $sortArray aggregation operator:

// {
//   service: { apps: { updates: [
//     { n: 1, date: ISODate("2012-03-10") },
//     { n: 2, date: ISODate("2012-01-10") },
//     { n: 5, date: ISODate("2012-07-10") }
//   ]}}
// }
db.collection.aggregate([
  { $set: {
    "service.apps.updates": {
      $sortArray: {
        input: "$service.apps.updates",
        sortBy: { date: 1 }
      }
    }
  }}
])
// {
//   service: { apps: { updates: [
//     { n: 2, date: ISODate("2012-01-10") },
//     { n: 1, date: ISODate("2012-03-10") },
//     { n: 5, date: ISODate("2012-07-10") }
//   ]}}
// }

This:

sorts ($sortArray) the service.apps.updates array (input: "$service.apps.updates")
by applying a sort on dates (sortBy: { date: 1 })
without having to apply a combination of expensive $unwind, $sort and $group stages

Rafiq · Accepted Answer · 2023-10-26 11:23:56Z

0

 const timelines = await Timeline.aggregate<TimelineDoc>([
    {
      $match: {
        $and: query,
      },
    },
    {
      $unwind: {
        path: '$badges',
        preserveNullAndEmptyArrays: true,
      },
    },
    { $sort: { 'badges.updatedAt': MongoSortOrder.Descending } },
    {
      $group: {
    _id: '$_id',
    badges: {
      $addToSet: {
        name: '$badges.name',
        thumbnail: '$badges.thumbnail',
        updatedAt: '$badges.updatedAt',
        user: '$badges.user',
      },
    },
    createdAt: { $first: '$createdAt' },
  },
    },
    { $sort: sort },
    { $skip: skip },
    { $limit: limit },
  ]);

answered Oct 26, 2023 at 11:23

Rafiq

12k5 gold badges44 silver badges46 bronze badges

Collectives™ on Stack Overflow

Mongodb sort inner array

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related