Mongo find query only returns one result

Question

Hello I have the following data structure :

[
  {
    "name": "a name",
    "project": [
      {
        companyName: "a name",
        contactPerson: [
          {
            work_email: "test@test.com"
          }
        ]
      },
      {
        companyName: "a name1",
        contactPerson: [
          {
            work_email: "test1@test.com"
          }
        ]
      },
      {
        companyName: "a name2",
        contactPerson: [
          {
            work_email: "test2@test.com"
          }
        ]
      },
      {
        companyName: "a name3",
        contactPerson: [
          {
            work_email: "test@test.com"
          }
        ]
      },
      
    ]
  }
]

With this query i want to find all projects that have the email test@test.com :

db.collection.find({
  "project.contactPerson.work_email": "test@test.com"
},
{
  "project.$": 1
})

It only returns the first result it finds and then it just stops. but in my data i have two projects with that email and i want to find both. here's a playground you can use to further help me if you can. Thanks in advance and much appreciated : https://mongoplayground.net/p/4Mpp7kHi98u

Answer 1

//step1(problem statement related find):, find shows all projects even one of the array element of contact is matched, use an aggregate function to display specific email ids

> db.test3.find({    "project.contact.email": "abc2@email.com" }).pretty();
{
        "_id" : ObjectId("5f43fdc153e34ac6967fe8ce"),
        "name" : "Pega Contractors",
        "project" : [
                {
                        "pname" : "pname1",
                        "contact" : [
                                {
                                        "email" : "xyz1@email.com"
                                }
                        ]
                },
                {
                        "pname" : "pname2",
                        "contact" : [
                                {
                                        "email" : "abc2@email.com"
                                }
                        ]
                },
                {
                        "pname" : "pname3",
                        "contact" : [
                                {
                                        "email" : "xyz1@email.com"
                                }
                        ]
                }
        ]
}
--
//aggregate option:

//Step1: data preparation
> db.test3.find().pretty();
{
        "_id" : ObjectId("5f43fdc153e34ac6967fe8ce"),
        "name" : "Pega Contractors",
        "project" : [
                {
                        "pname" : "pname1",
                        "contact" : [
                                {
                                        "email" : "xyz1@email.com"
                                }
                        ]
                },
                {
                        "pname" : "pname2",
                        "contact" : [
                                {
                                        "email" : "abc2@email.com"
                                }
                        ]
                },
                {
                        "pname" : "pname3",
                        "contact" : [
                                {
                                        "email" : "xyz1@email.com"
                                }
                        ]
                }
        ]
}
>

//step2: aggregate and unwind project for the next step pipeline input
> db.test3.aggregate([ {$unwind: "$project"}]);
{ "_id" : ObjectId("5f43fdc153e34ac6967fe8ce"), "name" : "Pega Contractors", "project" : { "pname" : "pname1", "contact" : [ { "email" : "xyz1@email.com" } ] } }
{ "_id" : ObjectId("5f43fdc153e34ac6967fe8ce"), "name" : "Pega Contractors", "project" : { "pname" : "pname2", "contact" : [ { "email" : "abc2@email.com" } ] } }
{ "_id" : ObjectId("5f43fdc153e34ac6967fe8ce"), "name" : "Pega Contractors", "project" : { "pname" : "pname3", "contact" : [ { "email" : "xyz1@email.com" } ] } }

//step3: Desired outcome, i.e display data specific to email
> db.test3.aggregate([
...    {$unwind: "$project"},
...    {$match: {"project.contact.email":"xyz1@email.com"}}
...    ]);
{ "_id" : ObjectId("5f43fdc153e34ac6967fe8ce"), "name" : "Pega Contractors", "project" : { "pname" : "pname1", "contact" : [ { "email" : "xyz1@email.com" } ] } }
{ "_id" : ObjectId("5f43fdc153e34ac6967fe8ce"), "name" : "Pega Contractors", "project" : { "pname" : "pname3", "contact" : [ { "email" : "xyz1@email.com" } ] } }
> db.test3.aggregate([    {$unwind: "$project"},    {$match: {"project.contact.email":"acb2@email.com"}}    ]);
> db.test3.aggregate([    {$unwind: "$project"},    {$match: {"project.contact.email":"abc2@email.com"}}    ]);
{ "_id" : ObjectId("5f43fdc153e34ac6967fe8ce"), "name" : "Pega Contractors", "project" : { "pname" : "pname2", "contact" : [ { "email" : "abc2@email.com" } ] } }
>

Answer 2

db.collection.aggregate([
  {
    $unwind: "$project"
  },
  {
    $match: {
      "project.contactPerson.work_email": "test@test.com"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "name": {
        "$first": "$name"
      },
      "project": {
        "$push": {
          "companyName": "$project.companyName",
          "contactPersion": "$project.contactPerson"
        }
      }
    }
  }
])

Answer 3

The positional $ operator limits the contents of an to return either:

1. The first element that matches the query condition on the array.
2. The first element if no query condition is specified for the array (Starting in MongoDB 4.4). Ref

You can do something like following,

[
  {
    "$unwind": "$project"
  },
  {
    $addFields: {
      "project.contactPerson": {
        $filter: {
          input: "$project.contactPerson",
          cond: {
            $eq: [
              "$$this.work_email",
              "test@test.com"
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $ne: [
          "$project.contactPerson",
          []
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      project: {
        "$addToSet": "$project"
      }
    }
  }
]

Working Mongo playground