mongodb - Mongo sort on a calculated condition

Question

I have a Mongo collection of messages that looks like this:

{
  'recipients': [],
  'unRead': [],
  'content': 'Text'
}

Recipients is an array of user ids, and unRead is an array of all users who have not yet opened the message. That's working as intended, but I need to query the list of all messages so that it returns the first 20 results, prioritizing the unread ones first, something like:

db.messages.find({recipients: {$elemMatch: userID} })
  .sort({unRead: {$elemMatch: userID}})
  .limit(20)

But that doesn't work. What's the best way to prioritize results based on whether they fit a certain criteria?

Answer 1

If you want to "weight" results by certain criteria or have any kind of "calculated value" within a "sort", then you need the .aggregate() method instead. This allows "projected" values to be used in the $sort operation, for which only a present field in the document can be used:

db.messages.aggregate([
    { "$match": { "messages": userId } },
    { "$project": {
        "recipients": 1,
        "unread": 1,
        "content": 1,
        "readYet": {
            "$setIsSubset": [ [userId], "$unread" ] }
        }
    }},
    { "$sort": { "readYet": -1 } },
    { "$limit": 20 }
])

Here the $setIsSubset operator allows comparison of the "unread" array with a converted array of [userId] to see if there are any matches. The result will either be true where the userId exists or false where it does not.

This can then be passed to $sort, which orders the results with preference to the matches ( decending sort is true on top ), and finally $limit just returns the results up to the amount specified.

So in order to use a calulated term for "sort", the value needs to be "projected" into the document so it can be sorted upon. The aggregation framework is how you do this.

Also note that $elemMatch is not required just to match a single value within an array, and you need only specify the value directly. It's purpose is where "multiple" conditions need to be met on a single array element, which of course does not apply here.