I have a collection that has four columns: object1, object2, selectedObject, unselectedObject
. object1
and object2
are two random objects from a collection (the collection contains the results of various matchups between two objects each time). I want to create a pipeline that shows for each distinct object in the object1
column, how many times it appeared in the selectedObject
column, and how many times it appeared in the unselectedObject
column. Grouping and filtering according to just one column seems easy enough, but I want to display the results from both columns side by side, and various methods I tried for doing so failed.
For example, if the collection I have is:
{ID: 1, Object1: A, Object2: B, selected: A, Unselected: B}
{ID: 1, Object1: A, Object2: C, selected: C, Unselected: A}
{ID: 1, Object1: A, Object2: B, selected: B, Unselected: A}
{ID: 1, Object1: B, Object2: A, selected: B, Unselected: A}
{ID: 1, Object1: A, Object2: C, selected: C, Unselected: A}
{ID: 1, Object1: C, Object2: A, selected: C, Unselected: A}
I would like the output to be something like:
{ Object: A, Selected: 1, Unselected: 5}
{ Object: B, Selected: 2, Unselected: 1}
{ Object: A, Selected: 3, Unselected: 0}
2
Answers
This will work:
Explanation:
Playground link.
I like @charchit-kapoor’s answer. And just to show another way (not as nice looking, nor probably as performant for small-ish collections) that might help if the entire collection is too large for a single
"$group"
stage, this also works.Try it on mongoplayground.net.