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! :)
CodePudding user response：
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']}} }
]);
CodePudding user response：
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
}
}
])