Mongodb conditional sort

Question

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      
      }
 }])

could you please post your current query result and your expectation? — Mosius, Mar 18 '19 at 06:48
@Mosius My current query result return all the featured posts in the top, But what I want is to return those featured posts in the top, who are posted one day ago, and the rest should be ignored in order, something like all new (1 day) featured jobs in top, then other ordering rules. — jones, Mar 18 '19 at 06:59

Mosius · Accepted Answer · 2019-11-07T09:33:52.583

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.

It's almost near to what I want but has one problem, it ignores other sorting rules for featured posts, suppose I have a new post created today, and one another `featured` post from yesterday, and 10 other posts from old. Now with this query, the new post is in the top, but that `featured` post is not the second (in fact it should be the second post because of its poblishDate) — jones, Mar 18 '19 at 09:24

Mongodb conditional sort

1 Answers1

Linked