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" : "abc@test.com"
            },
            "system" : {
                "value" : "email"
            },
            "useCode" : {
                "value" : "work"
            },
            "value" : {
                "value" : "abc@test.com"
            }
        }
    ]
}

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.

Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. If it does in fact answer the question you asked then please note to [Accept your Answers](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) to the questions you ask — turivishal, May 06 '21 at 10:28

score 0 · Answer 1 · answered Apr 30 '21 at 06:07

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.

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

1 Answers1