CODEWITHSUNDEEP
mongodbaggregation-framework
Geralt
Geralt

Reputation: 180

How to query $regex on embedded documents to count distinct values

I have a collection with documents in the following format:

{ 
    "_id" : ObjectId("abc"), 
    "mobile" : "123456789", 
    "channels" : [
        {
            "name" : "email.xz", 
            "type" : "a", 
            "properties" : [
                {
                    "provider" : "outlook"
                }
            ]
        }, 
        {
            "name" : "sms", 
            "type" : "sms"
            }
    ], 

}

I need to find customers who have at least one channel which the name ends with ".xz", but only if provider exists and has a value, and return the count of customers per channel. For instance:

email.xz = 10
sms.xz = 5
push.xz = 7

I tried to use the aggregate function below, but it resulted in a count of all the customers that match the criteria.

db.consumers.aggregate(
{$match: {"channels.name": {$regex: ".xz$"}, 
"notificationChannels.properties.provider": { $exists: true }}},
{$group: {_id: "channels.name"},
count: {$sum: 1}})

Upvotes: 1

Views: 259

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

$unwind the array, and then $match the same way again. The first match is only selecting documents, and you want to filter:

db.consumers.aggregate(
  { $match: {
    "channels.name": {$regex: ".xz$"}, 
    "channels.properties.provider": { $exists: true }
  }},
  { $unwind: "$channels" },
  { $match: {
    "channels.name": {$regex: ".xz$"}, 
    "channels.properties.provider": { $exists: true }
  }},
  { $group:{
    "_id": "$channels.name",
    "count": {$sum: 1}
  }}
)

Since you want to $group on a key from "inside" the array, then you need to $unwind it anyway. Since you only want the array entries that match your conditions, then the only way to do that with a $regex is through a $match after the $unwind

Upvotes: 2

Related Questions