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! :)

I think you'll need 2 examples of input docs and then the desired output docs shown with some comments/description about what is going on, esp. around what "my response" means and exactly how the sort should lay out. I think you're on the right track with the score field. — Buzz Moschetti
– Buzz Moschetti, Commented Mar 2, 2022 at 23:36
@HenryEcker, true. Sorry for the inconvenience. Posted it as an answer :) — Ertan Hasani
– Ertan Hasani, Commented Mar 4, 2022 at 20:15

Buzz Moschetti · Accepted Answer · 2022-03-03 01:48:28Z

1

Here is a slightly simplified input set. We also include a target field for help in testing the scoring algo; it is not necessary for the final pipeline, where score is A, B, C for first, middle, and last in sort order. The score can be "anything" as long as it sorts properly. I used A, B, and C because it is visually different than the response codes (0,1,2) we are looking at so the pipeline functions are a little more comprehensible but it could be 10, 20, 30 or 5,10,15.

var myUserId = 1;

var r = [
    {
        target: 'C', // last, myUserId response is 1                              
        responses: [
            {userId:0, response:0},
            {userId:1, response:1}
        ]
    }
    ,{
        target: 'C', // last, myUserId response is 1                              
        responses: [
            {userId:1, response:1},
            {userId:0, response:0}
        ]
    }
    ,{
        target: 'A', // first, myUserId response is 0                             
        responses: [
            {userId:1, response:0},
            {userId:0, response:0}
        ]
    }
    ,{
        target: 'B', // mid, all 1s                                               
        responses: [
            {userId:7, response:1},
            {userId:9, response:1}
        ]
    }
    ,{
        target: 'C',  // last, a 2 exists                                         
        responses: [
            {userId:4, response:2},
            {userId:3, response:1},
            {userId:1, response:0}
        ]
    }
];

This pipeline will produce the desired output:

db.foo.aggregate([
    {$addFields: {score:
          {$cond: [
              {$in: [2, '$responses.response']}, // any 2s?                       
              'C', // yes, assign last                                            

              {$cond: [ // else                                                   
                  // All responses 1 (i.e. set diff is from 1 is empty set []?    
                  {$eq: [ {$setDifference:['$responses.response',[1]]}, [] ] },
                  'B', // yes, assign mid                                         

                  {$cond: [ // else                                               
                      // Does myUserId have a response of 0?  filter the 
                      // array on these 2 fields and if the size of the 
                      // filtered array != 0, that means you found one!                     
                      {$ne:[0, {$size:{$filter:{input:'$responses',
                                cond:{$and:[
                                    {$eq:['$$this.userId',myUserId]},
                                    {$eq:['$$this.response',0]}
                                ]}
                           }} } ]},
                      'A', // yes, assign first                                   
                      'C',  // else last for rest                                 
                  ]}
          ]}
          ]}
    }}

    ,{$sort: {'score':1}}

    // TEST: Show items where target DOES NOT equal score.  If the pipeline
    // logic is working, this stage will produce zero output; that's
    // how you know it works.                       
    //,{$match: {$expr: {$ne:['$score','$target']}} }
]);

edited Mar 3, 2022 at 1:48

answered Mar 3, 2022 at 1:37

Buzz Moschetti

7,6433 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ertan Hasani Over a year ago

Thank you for your help! I believe this solution should work. Though, so some changes will be needed here. So i've decided that i want to ignore all items that have response 2 in this filtering, so I would add $match: { "$responses.response": { $ne: 2 } } before the $addFields. I came up with another solution instead of this. I will add it in the question. I would like to get your feedback on that and let me know what you think. Thanks!

Ertan Hasani Over a year ago

Updated the question with my solution :)

Buzz Moschetti Over a year ago

@ErtanHasani What happened to the all 1s condition? Well, in any event, the logic is even simpler now so if it works, then great. Make sure $responses array always exists even if empty because {$size: thingThatIsUnsetAKANull} will throw an error. How about marking the response with an accepted answer and upvote since it got you on your way?

Ertan Hasani Over a year ago

Yes. But now we will first sort by myScore: 1, which will show all items that i have scored as 0. Then we will score by totalScore: -1, which means that will show those that are all 1s first. Because totalScore will do sum(arr) / len(array), we can get percentage of how many have scored 1. If all have scored 1, that will be shown first as its totalScore: -1. Yes, that will always do, as it must be minimum 2 responses in order for an object to be created in collection. Yes, i will do it, since that is technically the answer of the first question. Thanks!

Ertan Hasani · Accepted Answer · 2022-03-04 20:14:31Z

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

Collectives™ on Stack Overflow

Performing sorting query in MongoDB

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related