Home > Software design >  MongoDB sort by dateTime and name
MongoDB sort by dateTime and name

Time:11-03

I have a similar collection:

{"name": 'C', "dateTime": "Oct 19 14:52"}
{"name": 'B', "dateTime": "Oct 19 14:52"}
{"name": 'A', "dateTime": "Oct 19 16:52"}
{"name": 'C', "dateTime": "Oct 19 15:52"}
{"name": 'B', "dateTime": "Oct 19 16:52"}

After I sort using

$sort: {
    "dateTime": 1,
    "name": 1
}

I got this result back:

{"name": 'B', "dateTime": "Oct 19 14:52"}
{"name": 'C', "dateTime": "Oct 19 14:52"}
{"name": 'C', "dateTime": "Oct 19 15:52"}
{"name": 'A', "dateTime": "Oct 19 16:52"}
{"name": 'B', "dateTime": "Oct 19 16:52"}

However, the result I want is after I sort by time, I want to group them like this:

{"name": 'B', "dateTime": "Oct 19 14:52"}
{"name": 'B', "dateTime": "Oct 19 16:52"}
{"name": 'C', "dateTime": "Oct 19 14:52"}
{"name": 'C', "dateTime": "Oct 19 15:52"}
{"name": 'A', "dateTime": "Oct 19 16:52"}

I would like to ask how can I achieve the result above, thanks

CodePudding user response:

You will first need to $group your documents according to name first. Use an array to hold the docs, $sort them according to your sort. $unwind them to get back the original doc.

db.collection.aggregate([
  {
    $group: {
      _id: "$name",
      docs: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $sort: {
      "docs.dateTime": 1,
      "docs.name": 1
    }
  },
  {
    "$unwind": "$docs"
  },
  {
    "$replaceRoot": {
      "newRoot": "$docs"
    }
  }
])

Here is the Mongo playground for your reference.

  • Related