CODEWITHSUNDEEP
mongodbmongoose
jones
jones

Reputation: 1453

Mongodb conditional sort

I want to add a field to the sort clause, for only those documents that created one day ago. Suppose in my collection mycollection my documents have a field publishDate, and some other fields, and following is my query:

db.getCollection('mycollection').aggregate([                                                                                                                         
{
    "$match": {   
        "expireDate": {  
            "$gte": ISODate("2019-03-14T00:00:00.000Z")
        },
        "publishDate": {  
            "$lt": ISODate("2019-03-15T00:00:00.000Z")
        },
        "isPublished": true,     
        "isDrafted": false,  
        "deletedAt": {      
            "$eq": null   
        },
        "deleted": false,  
        "blocked": {     
            "$exists": false  
        }
    }
 },
 {
     "$sort": {    
         "isFeatured": -1,   // What I want is to add this field in sort clause by condition, when publishDate is yesterday, otherwise ignore it
         "refreshes.refreshAt": -1,  
         "publishDate": -1,   
         "_id": -1   
     }
 },
 {  
     "$skip": 0  
 },
 {   
     "$limit": 12  
 },
 {
     "$project": {
         "position": 1      
      }
 }])

Upvotes: 7

Views: 5248

Answers (1)

Mosius
Mosius

Reputation: 1682

Create a virtual field which represents a value for the entries that should be shown on top of the list, then sort entries based on that field. You can use $addFields and $cond operators to accomplish it.

The implementation would be something like this:

// ...
{
  "$addFields": {
    "isFeaturedSort": {
      "$cond": {
        "if": {
          "$and": {
            "publishDate": {
              "$gte": ISODate("2019-03-14T00:00:00.000Z"),
            },
            "$eq": ["isFeatured", true],
          },
        },
        "then": 1,
        "else": 0,
      },
    },
  },
},
{
  "$sort": {
    "isFeaturedSort": -1, // changed
    "refreshes.refreshAt": -1,
    "publishDate": -1,
    "_id": -1,
  },
},
// ...

Please notice that $addField only work in MongoDB 3.4 and further. also the snippets code may contain errors.

Upvotes: 8

Related Questions