Conditional $addToSet using aggregation framework

Discussion:

Marc Tinkler

2013-02-28 14:37:46 UTC

Hi all, we're trying to use the aggregation framework to do what (to my
mind) should be a very simple operation: keep track the people who got a
set of questions right and wrong.

Given the input documents:
{ question: 1, user: 1, correct: true },
{ question: 1, user: 2, correct: false },
{ question: 2, user: 1, correct: false },
{ question: 2, user: 2, correct: false }

We'd like to produce the following output:
{ question: 1, rightusers: [ 1 ], wrongusers: [ 2 ] }
{ question: 2, rightusers: [ ], wrongusers: [ 1, 2 ] }

The best we can seem to do is this:
aggregate(
{ $group: { _id: '$question',
rightusers: { $addToSet: { $cond : [ '$correct', '$user', null
] } },
wrongusers: { $addToSet: { $cond : [ '$correct', null, '$user'
] } }
} },
{ $project : { _id: 0, 'question': '$_id', rightusers: 1, wrongusers: 1 } }
)

which produces:

{ "rightusers" : [ null ], "wrongusers" : [ 2, 1 ],"question" : 2 },
{ "rightusers" : [ null, 1 ], "wrongusers" : [ 2, null ], "question" : 1
}

Anyone have any idea how we can eliminate the nulls?

Any help would be greatly appreciated!

Thanks,
Marc
--
--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongodb-user-/***@public.gmane.org
To unsubscribe from this group, send email to
mongodb-user+unsubscribe-/***@public.gmane.org
See also the IRC channel -- freenode.net#mongodb

---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
For more options, visit https://groups.google.com/groups/opt_out.

Asya Kamsky

2013-03-01 09:45:51 UTC

Permalink

I understand this is a sample data set to test the query - just wondering
before suggesting an answer - would it be okay for there to be a null in
the array if there was no one who got the question right (or wrong)?

Asya

Post by Marc Tinkler
Hi all, we're trying to use the aggregation framework to do what (to my
mind) should be a very simple operation: keep track the people who got a
set of questions right and wrong.
{ question: 1, user: 1, correct: true },
{ question: 1, user: 2, correct: false },
{ question: 2, user: 1, correct: false },
{ question: 2, user: 2, correct: false }
{ question: 1, rightusers: [ 1 ], wrongusers: [ 2 ] }
{ question: 2, rightusers: [ ], wrongusers: [ 1, 2 ] }
aggregate(
{ $group: { _id: '$question',
rightusers: { $addToSet: { $cond : [ '$correct', '$user',
null ] } },
wrongusers: { $addToSet: { $cond : [ '$correct', null,
'$user' ] } }
} },
{ $project : { _id: 0, 'question': '$_id', rightusers: 1, wrongusers: 1 } }
)
{ "rightusers" : [ null ], "wrongusers" : [ 2, 1 ],"question" : 2 },
1 }
Anyone have any idea how we can eliminate the nulls?
Any help would be greatly appreciated!
Thanks,
Marc

--
--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongodb-user-/***@public.gmane.org
To unsubscribe from this group, send email to
mongodb-user+unsubscribe-/***@public.gmane.org
See also the IRC channel -- freenode.net#mongodb

---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
For more options, visit https://groups.google.com/groups/opt_out.

Asya Kamsky

2013-03-01 09:52:18 UTC

Permalink

Assuming you're okay with question that NO ONE got right or wrong not
showing up in your result set, here is where your aggregation needs to
start:

{$group: {_id: {q:"$question", c:"$correct"}, set:{$addToSet:"$user"}}}

This gives you (on your sample set:

[
{
"_id" : {
"q" : 2,
"c" : false
},
"set" : [
2,
1
]
},
{
"_id" : {
"q" : 1,
"c" : false
},
"set" : [
2
]
},
{
"_id" : {
"q" : 1,
"c" : true
},
"set" : [
1
]
}
]

Now you can continue with
{$group: {_id: "$_id.q", ...

to group right and wrong sets by question from each pair of documents and
then use $project to rename/prettify the whole result.

Asya
P.S. just realized that during $project you can even fill in empty array
for questions no one got right or wrong using $ifnull expression.