How to custom sort a field in MongoDB

Question

I have a collection called INFODOCS which has a field called ML_PRIORITY(HIGH/MEDIUM/LOW) and STATUS(True/False/Null). I want to determine count of STATUS for each ML_PRIORITY and then sort the ML_PRIORITY in order High, Medium and Low.

[
  {
    "_id": "1",
    "ML_PRIORITY" : "HIGH",
    "STATUS" : "True"
  },
  {
    "_id": "2",
    "ML_PRIORITY" : "HIGH",
    "STATUS" : ""
  },
  {
    "_id": "3",
    "ML_PRIORITY" : "HIGH",
    "STATUS" : "False"
  },
  {
    "_id": "4",
    "ML_PRIORITY" : "MEDIUM",
    "STATUS" : ""
  },
  {
    "_id": "5",
    "ML_PRIORITY" : "Low",
    "STATUS" : ""
  }
]

I was able to determine the count of STATUS for each ML_PRIORITY using below aggregation pipeline but I am not sure how can I custom sort the ML_PRIORITY as $sort has only two option (1 and -1)

db.collection.aggregate([
    {
        '$group': {
            '_id': '$ML_PRIORITY', 
            'QUALITYCHECKDONE': {
                '$sum': {
                    '$cond': [
                        {
                            '$eq': [
                                '$STATUS', 'TRUE'
                            ]
                        }, 1, 0
                    ]
                }
            }, 
            'QUALITYCHECKNOTDONE': {
                '$sum': {
                    '$cond': [
                        {
                            '$eq': [
                                '$STATUS', ''
                            ]
                        }, 1, 0
                    ]
                }
            }, 
            'QUALITYCHECKNOTREQ': {
                '$sum': {
                    '$cond': [
                        {
                            '$eq': [
                                '$STATUS', 'FALSE'
                            ]
                        }, 1, 0
                    ]
                }
            }
        }
    }, {
        '$project': {
            '_id': 0, 
            'ML_PRIORITY': '$_id', 
            'QUALITYCHECKDONE': 1, 
            'QUALITYCHECKNOTDONE': 1, 
            'QUALITYCHECKNOTREQ': 1
        }
    }
])

Example - https://mongoplayground.net/p/anAwoqZk2Ys

nimrod serok · Accepted Answer · 2023-01-13 11:56:02Z

1

One option is to replace your last step with 3 steps, in order to $set an order field, $sort, and $unset it:

[
 {$set: {
      order: {$indexOfArray: [["HIGH", "MEDIUM", "Low"], "$_id"]},
      "ML_PRIORITY": "$_id"
  }},
  {$sort: {order: 1}},
  {$unset: ["_id", "order"]}
]

See how it works on the playground example

answered Jan 13, 2023 at 11:56

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

How to custom sort a field in MongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related