How can I group boolean fields in mongodb?

user_7372449 0
Viewed 952

Here's a very simple data example.

[
  {
    "aaa": true,
    "bbb": 111,
    
  },
  {
    "aaa": false,
    "bbb": 111,
    
  }
]

Then, what query should be executed so that I can get the result like this?

[
  {
    "_id": "0",
    "bbb_sum": 222,
    "aaa_and": false,
    "aaa_or": true
  }
]

Actually, I've tried with a query like this

db.collection.aggregate([
  {
    "$group": {
      "_id": "0",
      "bbb_sum": {
        "$sum": "$bbb"
      },
      "aaa_and": {
        "$and": ["$aaa", true]
      },
      "aaa_or": {
        "$or": ["$aaa", false]
      }
    }
  }
])

But the Mongo Playground complains query failed: (Location40237) The $and accumulator is a unary operator, that's quite confusing.

You can also find this simple test case here https://mongoplayground.net/p/8dqtXJ93vIx

Also, I've searched for similar questions on both Google and Stackoverflow, but I can't find one.

Thanks in advance!

mongodb
3 Answers

Not like "$sum","$and" and "$or" are not aggregation operators that can be used in "$group". You can temporary save all the "aaa" field into an array and then use "$project" operator to process the data.

db.collection.aggregate([
  {
    "$group": {
      "_id": "0",
      "sum": {
        "$sum": "$bbb"
      },
      "aaa_all": {
        "$push": "$aaa"
      }
    }
  },
  {
    "$project": {
      "sum": 1,
      "aaa_and": {
        "$allElementsTrue": "$aaa_all"
      },
      "aaa_or": {
        "$anyElementTrue": "$aaa_all"
      }
    }
  }
])

Here is the case: https://mongoplayground.net/p/Y-Fs_Ch9lwk

user_66811450

$min and $max operators actually work with booleans too, false being considered smaller than true

thinking of them as 0 and 1 might be easier to understand :

  • $min: [a,…,n] will return 1/true only if all elements are 1/true => this is a AND
  • $max: [a,…,n] will return 0/false only if all elements are 0/false => this is a OR

(the operators will return booleans if input booleans, the analogy with numbers is only for the sake of comprehension)

So your request can simply become :

db.collection.aggregate([
  {
    "$group": {
      "_id": "0",
      "bbb_sum": {
        "$sum": "$bbb"
      },
      "aaa_and": {
        "$min": "$aaa"
      },
      "aaa_or": {
        "$max": "$aaa"
      }
    }
  }
])
user_43997250

You can do some logics as below

db.collection.aggregate([
  {
    "$group": {//Group by desired id
      "_id": null,
      "sum": {//Sum the value
        "$sum": "$bbb"
      },
      "aaa_and": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$aaa",
                true
              ]
            },
            "then": 1, //If true returns 1
            "else": 0 // else 0
          }
        }
      },
      "total": { //helper to do the logic
        $sum: 1
      }
    }
  },
  {
    $project: {
      aaa_and: {
        "$eq": [//If total matches with number of true, all are true
          "$total",
          "$aaa_and"
        ]
      },
      aaa_or: {
        "$ne": [//if value greater than 0, then there is at least one true
          "$aaa_and",
          "0"
        ]
      },
      sum: 1
    }
  }
])

playground

user_26941840
Related