CODEWITHSUNDEEP
mongodbmongodb-query
ankit lohiya
ankit lohiya

Reputation: 65

How to custom sort a field in MongoDB

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

Upvotes: 2

Views: 38

Answers (1)

nimrod serok
nimrod serok

Reputation: 16033

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

Upvotes: 1

Related Questions