Performing sorting query in MongoDB

Question

I want to make this complex sorting query in MongoDB but I am failing to achieve it.

The model in the collection looks like this:

_id: UUID('some-id'),
isDeleted: false,
date: ISODate('some-date'),
responses: [{
    _id: UUID('some-id'),
    userId: UUID('some-id'),
    response: 0
}, {
    _id: UUID('some-id'),
    userId: UUID('some-id'),
    response: 1
}]

One thing to keep in mind is that the responses array will always have 2 or 3 objects inside it. Not more, not less. Also, the response will only have three values, either 0, 1, or 2.

And what I want to do is that I want to sort them differently for each user, based on their response.

So let's say that my collection which is called Events has a lot of objects in the database. I want that when I filter them, the sorting will be done like this:

If my response is 0 and others are either 0 or 1, then sort them always first.

If all responses are 1, sort them after.

Others (if any response is 2, or if my response is 1 but others are 1 or 0), sort them last.

We can find if its my response by passing the userId in the query.

On top of that, I will need to have pagination so I will need to implement the $skip and $limit.

Was giving it a try with $unwind then $project trying to do some scoring based sorting, but couldn't achieve it.

The scoring sorting would look something like this:

if my response is 0 and others are 0 or 1 -> score = 100

if all responses are 1 -> score = 50

all others -> score = 0

In this way we could order them by score. But I dont know how I can actually create this property in the fly.

Was thinking that we could create one property like this:

$project: {
    myScore: {
        $cond: {
            if: {
                $in: [
                    UUID('my-user-id'),
                    "$responses.userId"
                ],
                then: "$respones.response", //this is returning array here with all responses
                else: 0
            }
        }
    },
    totalScore: {
        $sum: "$respones.response"
    }
}

And then we would be able to do another stage where we sort on these numbers somehow.

Thank you! :)

Answer 1

Anyone wondering about this, here's what I came up with. p.s. I also decided that I need to ignore all items if any response contains response 2, so I will focus only on values 0 and 1.

db.invites.aggregate([
    {
        $match: {
            "$responses.response": {
                $ne: 2
            }
        }
    },
    {
        $addFields: {
            "myScore": {
              "$let": {
                "vars": {
                       "invite": {
                          // get only object that contains my userId and get firs item from the list (as it will always be one in the list)
                          "$arrayElemAt": [{
                            "$filter": {
                                  "input": "$responses",
                                  "as": "item",
                                  "cond": {"$eq": ["$$item.userId", UUID('some-id')]}
                          }} ,0]
                        }   
                  },
                  // ger response value of that object that contains my userId
                  "in": "$$invite.response"
              }
            },
            // as they are only 0 and 1s in the array items, we can see how many percent have voted with one.
            // totalScore = sum(responses.response) / size(responses)
            "totalScore": {
              $divide: [{$sum: "$responses.response"} , {$size: "$responses"}]
            }
        }
    },
    {
        $sort: {
            //sort by my score, so if I have responded with 0, show first
            "myScore": 1,
            //sort by totalScore, so if I have responded 1, show those that have all 1s first.
            "totalScore": -1
        }
    }
])