I have a document like this:

{"_id" : ObjectId("5836b91788538303"),
 "Name" : "Maria",
 "Email" : "[email protected]",
 "Age" : 34,
 "Contacts": [
      {"Contact_user" : {
               "_id" : ObjectId("5836b916885383"),
               "Name" : "Alejandro",
               "Email" : "[email protected]",
               "Age" : 32}},

      {"Contact_user" : {
               "_id" : ObjectId("5836b916888956"),
               "Name" : "Victor",
               "Email" : "[email protected]",
               "Age" : 41}},

       {"Contact_user" : {
               "_id" : ObjectId("5836b9168880987"),
               "Name" : "Agata",
               "Email" : "[email protected]",
               "Age" : 37}},
         ...

      ]}

The first thing I need is to match "Email" with "[email protected]" and the second step is to filter the subdocuments in order to get the contacts who are older or equal to 36 years old. I have tried unwind, double match condition, filter... and I don't get into the solution.

Furthermore I need an output like this:

{"Email" : "[email protected]", "Contact_email" : "[email protected]", "Contact_age" : 41}
{"Email" : "[email protected]", "Contact_email" : "[email protected]", "Contact_age" : 37}
{"Email" : "[email protected]", "Contact_email" : "[email protected]", "Contact_age" : 36}

How can I manage in order to repeat the "Email" in each output sentence?

Thank you in advance

CodePudding user response：

You can try a query,

• $match your required conditions
• $unwind deconstruct the Contacts array
• $match your required conditions
• $project to show required fields

db.collection.aggregate([
  {
    $match: {
      Email: "[email protected]"
    }
  },
  { $unwind: "$Contacts" },
  {
    $match: {
      "Contacts.Contact_user.Age": {
        $gte: 36
      }
    }
  },
  {
    $project: {
      _id: 0,
      Email: 1,
      Contact_email: "$Contacts.Contact_user.Email",
      Contact_age: "$Contacts.Contact_user.Age"
    }
  }
])

Playground