Mongo: how to sort by external weight

Question

Following this question which @NeilLunn has gracefully answered, here is my problem in more detail.

This is the set of documents, some have user_id some don't. The user_id represent the user who created the document:

{ "user_id" : 11, "content" : "black", "date": somedate }
{ "user_id" : 6, "content" : "blue", "date": somedate }
{ "user_id" : 3, "content" : "red", "date": somedate }
{ "user_id" : 4, "content" : "black", "date": somedate }
{ "user_id" : 4, "content" : "blue", "date": somedate }
{ "user_id" : 90, "content" : "red", "date": somedate }
{ "user_id" : 7, "content" : "orange", "date": somedate }
{ "content" : "orange", "date": somedate }
{ "content" : "red", "date": somedate }
...
{ "user_id" : 4, "content" : "orange", "date": somedate }
{ "user_id" : 1, "content" : "orange", "date": somedate }
{ "content" : "red", "date": somedate }
{ "user_id" : 90, "content" : "purple", "date": somedate }

The front end is pulling pages, so each page will have 10 items and I do that with limit and skip and it is working very well.

In case we have a logged in user, I would like to display to that current logged in user documents which he may find more interesting first, based on the users he interacted with.

The list of users which the current user may find interesting is sorted by score and is located outside of mongo. So the first element is the most important user which I would like to show his documents first, and the last user on the list is the least important.

The list is a simple array which looks like this: [4,7,90,1].

The system which created this user score is not located within mongo, but I can copy the data if that will help. I can also change the array to include a score number.

What I would like accomplish is the following:

Get the documents sorted by importance of the user_id from the list, so that documents from user_id 4 will be the first to show up, documents from user_id 7 second and so on. When where are no users left on the list I would like to show the rest of the documents. Like this:

1. all documents with user_d:4
2. all documents with user_d:7
3. all documents with user_d:90
4. all documents with user_d:1
5. all the rest of the documents

How should I accomplish this? Am I asking too much from mongo?

Answer 1

Given the array [4,7,90,1] what you want in your query is this:

db.collection.aggregate([
   { "$project": {
       "user_id": 1,
       "content": 1,
       "date": 1,
       "weight": { "$or": [
           { "$eq": ["$user_id": 4] }, 
           { "$eq": ["$user_id": 7] }, 
           { "$eq": ["$user_id": 90] }, 
           { "$eq": ["$user_id": 1] }, 
       ]}
   }},
   { "$sort": { "weight": -1, "date": -1 } }
])

So what that does is, for every item contained in that $or condition, the user_id field is tested against the supplied value, and $eq returns 1 or 0 for true or false.

What you do in your code is for each item you have in the array you build the array condition of $or. So it's just creating a hash structure for each equals condition, passing it to an array and plugging that in as the array value for the $or condition.

I probably should have left the $cond operator out of the previous code so this part would have been clearer.

Here's some code for the Ruby Brain:

userList = [4, 7, 90, 1];

orCond = [];

userList.each do |userId|
  orCond.push({ '$eq' => [ 'user_id', userId ] })
end

pipeline = [
    { '$project' => {
        'user_id' => 1,
        'content' => 1,
        'date' => 1,
        'weight' => { '$or' => orCond }
    }},
    { '$sort' => { 'weight' => -1, 'date' => -1 } }
]

If you want to have individual weights and we'll assume key value pairs, then you need to nest with $cond :

db.collection.aggregate([
   { "$project": {
       "user_id": 1,
       "content": 1,
       "date": 1,
       "weight": { "$cond": [
           { "$eq": ["$user_id": 4] },
           10,
           { "$cond": [ 
               { "$eq": ["$user_id": 7] },
               9,
               { "$cond": [
                   { "$eq": ["$user_id": 90] },
                   7,
                   { "$cond": [
                       { "$eq": ["$user_id": 1] },
                       8, 
                       0
                   ]}
               ]}
           ]}
       ]}
   }},
   { "$sort": { "weight": -1, "date": -1 } }
])

Note that it's just a return value, these do not need to be in order. And you can think about the generation of that.

For generating this structure see here:

https://stackoverflow.com/a/22213246/2313887

Answer 2

Since mongoDB version 3.2 we can use a $filter which make this much easier to maintain in case there are more than 4 scores:

db.collection.aggregate([
  {
    $addFields: {
      weight: [
        {key: 4, score: 10}, {key: 8, score: 9}, {key: 90, score: 8}, {key: 1, score: 7}       
      ]
    }
  },
  {
    $addFields: {
      weight: {
        $filter: {
          input: "$weight",
          as: "item",
          cond: {$eq: ["$$item.key", "$user_id"]}
        }
      }
    }
  },
  {
    $set: {
      weight: {
        $cond: [{$eq: [{$size: "$weight"}, 1]}, {$arrayElemAt: ["$weight", 0]}, {score: 1}]
      }
    }
  },
  {$set: {weight: "$weight.score"}},
  {$sort: {weight: -1, date: -1}}
])

See how it works on the playground example