Sort DD/MM date on a .find() with Mongoose-CodePudding

I was trying to find how can I sort birthdays date by day and months with a .find() performed by mongoose.
Actual code:

const embed2 = new MessageEmbed()
.setColor('#FBC78E')
.setTitle(`Liste des anniversaires de ${message.guild.name}`)
birthday.find({}).then(data => {
  embed2
  .setDescription(Array.from(data).map(u => `<@${u.user_id}> : ${u.birthday}`))

  loading_message.delete();
  message.guild.channels.resolve(args[1]).send(embed2)
});

The problem is that when I try to do add a .sort('birthday') to my .find(), it only filters the days, but it's not what I'm searching for, here's the result of the .find() (without the .sort()):

[
  {
    _id: new ObjectId("620d763c86159915746f431b"),
    user_id: '493470054415859713',
    birthday: '04/11',
    __v: 0
  },
  {
    _id: new ObjectId("620d770505fa9fe0910968c0"),
    user_id: '933751655046979645',
    birthday: '18/05',
    __v: 0
  },
  {
    _id: new ObjectId("620d77575417ab94a5f2c837"),
    user_id: '735987183714041867',
    birthday: '15/12',
    __v: 0
  },
  {
    _id: new ObjectId("620d85fed17e202ed6d2b847"),
    user_id: '320851795058360331',
    birthday: '03/11',
    __v: 0
  }
]

Excepted result:

[
  {
    _id: new ObjectId("620d770505fa9fe0910968c0"),
    user_id: '933751655046979645',
    birthday: '18/05',
    __v: 0
  },
  {
    _id: new ObjectId("620d85fed17e202ed6d2b847"),
    user_id: '320851795058360331',
    birthday: '03/11',
    __v: 0
  },
  {
    _id: new ObjectId("620d763c86159915746f431b"),
    user_id: '493470054415859713',
    birthday: '04/11',
    __v: 0
  },
  {
    _id: new ObjectId("620d77575417ab94a5f2c837"),
    user_id: '735987183714041867',
    birthday: '15/12',
    __v: 0
  }
]

As you can see here, the birthdays are sorted by day AND month, which is what I try to do. Thanks.

CodePudding user response：

When you sort the field values "04/11", "18/05", "15/12", "03/11" - the sorting happens as "03/11", "04/11", "15/12", "18/05". It is by the string values - where "0" is less than "1" and so on. Since the birthday data is of the format "DD/MM" you cannot get the correct sorting you are looking for, i.e., by MM and DD. The data format need to be "MM/DD". The way to do this is to transform the format and then sort with the newly formatted values.

For example, you can use aggregation operators to switch the values of DD and MM in the birthday field and create a new field called birthday_MM_DD and use it for sorting. Note the code runs in mongosh or mongo shell.

var pipeline = [

  // Create a new birthday field with MM/DD format (a temporary field)
  { 
    $addFields: {
        birthday_MM_DD: { 
            $concat: [ 
                { $substrCP: [ "$birthday", 3, 2 ] }, 
                "/", 
                { $substrCP: [ "$birthday", 0, 2 ] } 
            ] 
        }
    }
  }, 

  // Sort with the newly formatted birthday field
  { 
    $sort: { 
        birthday_MM_DD: 1 
    }
  },

  // Remove the temporary birthday field
  // (as it is no longer needed)
  { 
    $unset: 
        "birthday_MM_DD" 
  },
]

db.collection.aggregate(pipeline)

The result has the documents ~~formatted~~ sorted correctly by birthday, as expected: "18/05", "03/11", "04/11", "15/12"