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}

CodePudding user response：

This will work:

db.collection.aggregate([
  {
    "$group": {
      "_id": null,
      "distinctTypes": {
        "$addToSet": "$Object1"
      },
      docs: {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$unwind": "$distinctTypes"
  },
  {
    "$project": {
      Object: "$distinctTypes",
      Selected: {
        "$size": {
          "$filter": {
            "input": "$docs",
            "as": "item",
            "cond": {
              "$eq": [
                "$$item.selected",
                "$distinctTypes"
              ]
            }
          }
        }
      },
      Unselected: {
        "$size": {
          "$filter": {
            "input": "$docs",
            "as": "item",
            "cond": {
              "$eq": [
                "$$item.Unselected",
                "$distinctTypes"
              ]
            }
          }
        }
      },
      _id: 0
    }
  }
])

Explanation:

1. Group the documents into a list, and find distinct object types using the $group.
2. Unwind the distinct object type using $unwind.
3. Calculate Selected and unselected counts using $filter and $size.

Playground link.

CodePudding user response：

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.

db.collection.aggregate([
  {
    "$facet": {
      "Objects": [
        {
          "$group": {
            "_id": "$Object1"
          }
        }
      ],
      "Selected": [
        {
          "$group": {
            "_id": "$selected",
            "count": {"$count": {}}
          }
        }
      ],
      "Unselected": [
        {
          "$group": {
            "_id": "$Unselected",
            "count": {"$count": {}}
          }
        }
      ]
    }
  },
  {
    "$project": {
      "Objects": {
        "$map": {
          "input": "$Objects",
          "as": "object",
          "in": {
            "Object": "$$object._id",
            "Selected": {
              "$cond": [
                {"$in": ["$$object._id", "$Selected._id"]},
                {
                  "$arrayElemAt": [
                    "$Selected.count",
                    {"$indexOfArray": ["$Selected._id", "$$object._id"]}
                  ]
                },
                0
              ]
            },
            "Unselected": {
              "$cond": [
                {"$in": ["$$object._id", "$Unselected._id"]},
                {
                  "$arrayElemAt": [
                    "$Unselected.count",
                    {"$indexOfArray": ["$Unselected._id", "$$object._id"]}
                  ]
                },
                0
              ]
            }
          }
        }
      }
    }
  },
  {"$unwind": "$Objects"},
  {"$replaceWith": "$Objects"}
])

Try it on mongoplayground.net.