Mongodb aggregate group on conditions-CodePudding

I have this collection where I have multiple properties to combine in an aggregation with $group.

const mongodocs = [
  {
    checks: {
      check1: "YES",
      check2: "NO"
    }
  },
  {
    checks: {
      check1: "YES",
      check2: "YES"
    }
  },
  {
    checks: {
      check1: "NO",
      check2: "YES"
    }
  },
  {
    checks: {
      check1: "YES",
      check2: "YES"
    }
  }
]

Expected output after aggregation:

const expectedoutput = {
  "YES": 2,
  "NO": 2
}

The condition for "YES" is that both check1 and check2 are YES

I think its something with $group where you add conditions for each group.

Any ideas?

    $group: {
      _id: { YES: { check1: "$checks.check1", check2: "$checks.check2" } }, // Add some condition here where it only counts yes if both check1 and check2 is equal to yes?
      count: { $sum: 1 },
    },
  },

CodePudding user response：

Query

you can do it by grouping by null(all collection 1 group), creating those 2 groups and sum with condition
the first sum the yes if both are yes
the second sum the no if at least one is no

Playmongo

aggregate(
[{"$group": 
   {"_id": null,
    "YES": 
     {"$sum": 
       {"$cond": 
         [{"$and": 
             [{"$eq": ["$checks.check1", "YES"]},
               {"$eq": ["$checks.check2", "YES"]}]},
          1, 0]}},
    "NO": 
     {"$sum": 
       {"$cond": 
         [{"$or": 
             [{"$eq": ["$checks.check1", "NO"]},
               {"$eq": ["$checks.check2", "NO"]}]},
          1, 0]}}}},
 {"$unset": ["_id"]}])