How to get result using stable sort using mongodb

Viewed 89

I am working on making a query which can sort the result after grouping keys in MongoDB.

Following is the example data in DB

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "code": "code",
    "groupId": "L0LV7ENT",
    "version": {
      "id": "1.0.0.0"
    },
    "status": "Done",
    "type": "main"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "code": "code",
    "groupId": "L0LV7ENT",
    "version": {
      "id": "2.0.0.0"
    },
    "status": "Done",
    "type": "main"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "code": "code",
    "groupId": "F6WJ9QP7",
    "version": {
      "id": "1.1.0.0"
    },
    "status": "Done",
    "type": "main"
  }
]

Here, I would like to sort the result in ascending order according to the version.id and to group the result according to the groupId.

Hence, I used the following query

db.collection.aggregate([
  {
    "$match": {
      "$and": [
        {
          "type": "main",
          "code": {
            "$in": [
              "code"
            ]
          },
          "status": {
            "$in": [
              "Done",
              "Completed"
            ]
          },
          "groupId": {
            "$in": [
              "L0LV7ENT",
              "F6WJ9QP7"
            ]
          }
        }
      ]
    }
  },
  {
    "$sort": {
      "_id": 1,
      "version.id": 1
    }
  },
  {
    "$group": {
      "_id": {
        "groupId": "$groupId"
      },
      "services": {
        "$push": "$$ROOT"
      }
    }
  }
])

But the result I am getting is not stable. Sometimes I see, the data with "_id": ObjectId("5a934e000102030405000002") coming first then ObjectId("5a934e000102030405000000") and ObjectId("5a934e000102030405000001").

It seems intermmitent. Is there any way to get a stable result?

EDIT

You can try it here

1 Answers

From the documentation:

$group does not order its output documents.

So you will need to sort after the group stage to have a deterministic output order.

Related