How to use conditional statement for sort in Mongodb-CodePudding

let me illustrate with an example, suppose my collection is like this,

products = [
    {
        id: 1,
        name: "a",
        offer: true,
        expiryDate: "23-03-2022"
    },

    {
        id: 2,
        name: "b",
        offer: false,//no offer

    },
    {
        id: 3,
        name: "c",
        offer: true,
        expiryDate: "01-01-2021"//already expired
    },
    {
        id: 4,
        name: "d",
        offer: true,
        expiryDate: "01-06-2022"
    },
]

I want here, the offered items comes first in descending order, it should not expire and then the remaining item should come in descending order so result would be like this

   [


    {
        id: 4,
        name: "d",
        offer: true,
        expiryDate: "01-06-2022"
    },
    {
        id: 1,
        name: "a",
        offer: true,
        expiryDate: "23-03-2022"
    },
    {
        id: 3,
        name: "c",
        offer: true,
        expiryDate: "01-01-2021"
    },

    {
        id: 2,
        name: "b",
        offer: false,

    },


]

CodePudding user response：

db.collection.aggregate([
  {
    "$match": {}
  },
  {
    "$set": {
      "offer": {
        "$cond": {
          "if": {
            "$lt": [
              {
                "$toDate": "$expiryDate"
              },
              "$$NOW"
            ]
          },
          "then": false,
          "else": true
        }
      },
      "expiryDate": {
        "$toDate": "$expiryDate"
      }
    }
  },
  {
    "$sort": {
      "expiryDate": -1,
      "offer": -1
    }
  }
])

mongoplayground