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

Question

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}})

Neil Lunn · Accepted Answer · 2018-05-16 08:24:34Z

2

$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

edited May 16, 2018 at 8:24

answered May 15, 2018 at 13:03

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kailash yogeshwar Over a year ago

$match is redundant in this query

Neil Lunn Over a year ago

@kailashyogeshwar No it certainly is not. It is of the "utmost importance" that you always use an initial stage in any aggregation pipeline that can address an index and ideally reduce results. You appear to be presuming that "every document" will meet the condition and that you only ever need the subsequent $match following $unwind to filter the array content. Unfortunately you probably picked up that opinion from seeing very bad examples before that do not consider that. Both $match stages here are doing two different things. So it's not a mistake but intentional and good design.

Gerard H. Pille Over a year ago

perhaps using '_id: "$channels.name"' for the $group, would be more informative?

Neil Lunn Over a year ago

@GerardH.Pille Thanks. Overzealous copy and paste and did not notice that.

Gerard H. Pille Over a year ago

Thank you. Never worked with MongoDB, got nowhere with this exercise after a whole day of trying. The fact that I didn't realize I was using a 3.2 (on Debian) but looking at a 3.6 manual, didn't improve my chances. Your answer should appear in the tutorials.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related