Write a mongo query to count the data where similar data in array?

Question

Sample data: there are multiple similar collection:

{
    "_id" : NumberLong(301),
   
    "telecom" : [ 
        {
            "countryCode" : {
                "value" : "+1"
            },
            "extension" : [ 
                {
                    "url" : "primary",
                    "value" : [ 
                        "true"
                    ]
                }
            ],
            "modifiedValue" : {
                "value" : "8887778888"
            },
            "system" : {
                "value" : "phone"
            },
            "useCode" : {
                "value" : "Home Phone"
            },
            "value" : {
                "value" : "8887778888"
            }
        }, 
        {
            "extension" : [ 
                {
                    "url" : "primary",
                    "value" : [ 
                        "true"
                    ]
                }
            ],
            "modifiedValue" : {
                "value" : "[email protected]"
            },
            "system" : {
                "value" : "email"
            },
            "useCode" : {
                "value" : "work"
            },
            "value" : {
                "value" : "[email protected]"
            }
        }
    ]
}

Issue: I want to cont the collection where telecom.system.value = email and countryCode doesn't exist in the email part object. here I am attaching a script but I need one line query

var count = 0,i;
  db.getCollection('practitioner').find({"telecom.system.value":"email"}).forEach(function(practitioner){
    //print("updating : " +practitioner._id.valueOf())
    telecom = practitioner.telecom.valueOf()
    for(i= 0;i<telecom.length;i++){
        if(telecom[i].system.value === 'email' && telecom[i].countryCode){
        count+=1;
             }
    }
  });
  
    print(" Total count of the practitioner with country code in email object: "+count)

Above mention, the script is working fine and the output is as I expected. but the script is not optimised and I want to write in a single line query. Thanks in advance.

Answer 1

You can try aggregation method aggregate(),

Approach 1:

• $match condition for countryCode should exists and system.value should be email
• $filter to iterate loop of telecom array and check both condition, this will return expected elements
• $size to get total element from above filter result
• $group by null and count total

var result = await db.getCollection('practitioner').aggregate([
  {
    $match: {
      telecom: {
        $elemMatch: {
          countryCode: { $exists: true },
          "system.value": "email"
        }
      }
    }
  },
  {
    $project: {
      count: {
        $size: {
          $filter: {
            input: "$telecom",
            cond: {
              $and: [
                { $ne: [{ $type: "$$this.countryCode" }, "missing"] },
                { $eq: ["$$this.system.value", "email"] }
              ]
            }
          }
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      count: { $sum: "$count" }
    }
  }
]);

print("Total count of the practitioner with country code in email object: "+result[0].count);

Playground

---

Approach 2:

• $match condition for countryCode should exists and system.value should be email
• $unwind deconstruct telecom array
• $match to filter document using above conditions
• $count to get total elements count

var result = await db.getCollection('practitioner').aggregate([
  {
    $match: {
      telecom: {
        $elemMatch: {
          countryCode: { $exists: true },
          "system.value": "email"
        }
      }
    }
  },
  { $unwind: "$telecom" },
  {
    $match: {
      "telecom.countryCode": { $exists: true },
      "telecom.system.value": "email"
    }
  },
  { $count: "count" }
]);

print("Total count of the practitioner with country code in email object: "+result[0].count);

Playground

I have not tested the performance but you can check and use as per your requirement.