How to sort null value at the end while in Ascending MongoDB-CodePudding

I have multiple documents and some of the document does not contain that key which I want to sort so it is treated as null and when I sort then null data came first but I need to take preference other one and null placed at the end. sample data:

[
   {
      "cat_id":1,
      "categoryCode":"categoryCode1",
      "categoryName":"categoryName1",
      "cat_type":"A",
      "description":"Mens Upper Shirt"
   },
   {
      "cat_id":2,
      "categoryCode":"categoryCode2",
      "categoryName":"categoryName2",
      "cat_type":"A",
      "rank":5,
      "description":"Shirt"
   },
   {
      "cat_id":3,
      "categoryCode":"categoryCode3",
      "categoryName":"categoryName3",
      "cat_type":"Women Top wear",
      "description":"cloths"
   },
   {
      "cat_id":4,
      "categoryCode":"categoryCode4",
      "categoryName":"categoryName4",
      "cat_type":"A",
      "rank":8,
      "description":"Women"
   }
]

in above example cat_id- 1 and 3 does not contains rank field and it output response it came at last. expected output:

[
   {
      "cat_id":2,
      "categoryCode":"categoryCode2",
      "categoryName":"categoryName2",
      "cat_type":"A",
      "rank":5,
      "description":"Shirt"
   },
   {
      "cat_id":4,
      "categoryCode":"categoryCode4",
      "categoryName":"categoryName4",
      "cat_type":"A",
      "rank":8,
      "description":"Women"
   },
   {
      "cat_id":1,
      "categoryCode":"categoryCode1",
      "categoryName":"categoryName1",
      "cat_type":"A",
      "description":"Mens Upper Shirt"
   },
   {
      "cat_id":3,
      "categoryCode":"categoryCode3",
      "categoryName":"categoryName3",
      "cat_type":"Women Top wear",
      "description":"cloths"
   }
]

I am using this query-

db.collection.aggregate([
  {
    $addFields: {
      sortrank: {
        $cond: {
          if: {
            $eq: [
              "$rank",
              null
            ]
          },
          then: 2,
          else: 1
        }
      }
    }
  },
  {
    "$sort": {
      "sortrank": 1
    }
  }
])

CodePudding user response：

I think your direction of using an auxiliary field to sort is correct. You can use $ifNull to assign a dense index(e.g. 999) as your sort rank.

db.collection.aggregate([
  {
    "$addFields": {
      "sortrank": {
        "$ifNull": [
          "$rank",
          999
        ]
      }
    }
  },
  {
    $sort: {
      sortrank: 1
    }
  },
  {
    $project: {
      sortrank: false
    }
  }
])

Here is the Mongo playground for your reference.