mongodbaggregation-frameworkaggregate-functionsgroup

MongoDB using $regex in group aggregation


I have a sample data like this :

db.contact.insert(
    [
      { _id: 1, name: 'John legend',age: 35},
      { _id: 2, name: 'michael Ava',age: 40},
      { _id: 3, name: 'Tom holand',age: 20},
      { _id: 4, name: 'Frido pex',age: 25},
      { _id: 5, name: 'Daniel Gunava',age: 30},
      { _id: 6, name: 'Mert Brava',age: 30}
     ]

I want to group and count how many people with the name containing the word "ava" in the name and the other group not containing ava.

I know how to use both $regex and group in function like this

db.contact.find({name: {$regex: /ava/,$options: "i" }});
db.contact.aggregate([{$group: {_id: "$name",count: {$sum: 1},}}]);

how can I combine the 2 functions above. any help appreciated

I've tried this (it didn't work)

db.contact.aggregate([
  {
    $group: {
      _id: "$name",
      {$regex: /ava/,$options: "i" }
      count: {$sum: 1},
    }
  }
]);

Solution

  • The easiest way would be to group items by null, and create new fields that hold sum of the fields that pass the desired regex as below.

    db. contact.aggregate({
      "$group": {
        "_id": null,
        "namesWithAva": {
          "$sum": {
            "$cond": [
              { "$regexMatch": { "input": "$name", "regex": /ava/i } },
              1,
              0
            ]
          }
        },
        "namesWithoutAva": {
          "$sum": {
            "$cond": [{ "$not": { "$regexMatch": { "input": "$name", "regex": /ava/i } } }, 1, 0]
          }
        }
      }
    })