Project double nested array mongodb

Question

{
    _id: 'uniquewId',
    programs: [
        {
            progress: '5',
            addedBy: 'coach'
            exercises: [
                {
                     date: '1/12/20',
                     exercises: [
                         {
                              exId: 'pushup',
                              progress: 5
                         },
                         {
                              exId: 'situp',
                              progress: 5
                         },
                         {
                              exId: 'pushup',
                              progress: 0
                         }
                     ] 
                },
                {
                    date: '2/12/20',
                     exercises: [
                         {
                              exId: 'pushup',
                              progress: 5
                         },
                         {
                              exId: 'situp',
                              progress: 5
                         },
                         {
                              exId: 'pushup',
                              progress: 0
                         }
                     ] 
                }   
            ]
        },
        {
            progress: '5',
            addedBy: 'coach2'
            exercises: [
                {
                     date: '1/12/20',
                     exercises: [
                         {
                              exId: 'pushup',
                              progress: 5
                         },
                         {
                              exId: 'situp',
                              progress: 5
                         },
                         {
                              exId: 'pushup',
                              progress: 0
                         }
                     ] 
                },
                {
                    date: '2/12/20',
                     exercises: [
                         {
                              exId: 'pushup',
                              progress: 5
                         },
                         {
                              exId: 'situp',
                              progress: 5
                         },
                         {
                              exId: 'pushup',
                              progress: 0
                         }
                     ] 
                }   
            ]
        }
    ]
}

That above is how my document looks . Is there a way i can project the programs array so it has the documents which have addedBy = 'coach' and the exercises array inside exercises array should only have exId = 'pushup'

the output i want is:

{
    _id: 'uniquewId',
    programs: [
        {
            progress: '5',
            addedBy: 'coach'
            exercises: [
                {
                     date: '1/12/20',
                     exercises: [
                         {
                              exId: 'pushup',
                              progress: 5
                         },
                         {
                              exId: 'pushup',
                              progress: 0
                         }
                     ] 
                },
                {
                    date: '2/12/20',
                     exercises: [
                         {
                              exId: 'pushup',
                              progress: 5
                         },
                         {
                              exId: 'pushup',
                              progress: 0
                         }
                     ] 
                }   
            ]
        }
    ]
}

the program array may have more than one document which have addedBy='coach' if so it should all be there :)

Answer 1

Double $map and double $filter will do the trick:

db.collection.aggregate([
    {
        $project: {
            _id: 1,
            programs: {
                $map: {
                    input: { $filter: { input: "$programs", cond: { $eq: [ "$$this.addedBy", "coach" ] } } },
                    in: {
                        progress: "$$this.progress",
                        addedBy: "$$this.addedBy",
                        exercises: {
                            $map: {
                                input: "$$this.exercises",
                                in: {
                                    date: "$$this.date",
                                    exercises: {
                                        $filter: {
                                            input: "$$this.exercises",
                                            as: "e",
                                            cond: {
                                                $eq: [ "$$e.exId", "pushup" ]
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
])

Mongo Playground

Answer 2

I don't think projection alone can help you achieve the result you described. Try this:

db.collection.aggregate([
  {
    $unwind: "$programs"
  },
  {
    $match: {
      "programs.addedBy": "coach"
    }
  },
  {
    $project: {
      programs: {
        progress: "$programs.progress",
        addedBy: "$programs.addedBy",
        exercises: {
          $map: {
            input: "$programs.exercises",
            as: "exercise",
            in: {
              date: "$$exercise.date",
              exercises: {
                $filter: {
                  input: "$$exercise.exercises",
                  as: "exerciseDetails",
                  cond: {
                    $eq: [
                      "$$exerciseDetails.exId",
                      "pushup"
                    ]
                  }
                }
              }
            }
          },

        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      programs: {
        $push: "$programs"
      }
    }
  }
])

Why this should work:

Above is an aggregation to get the described output. I'm using 4 aggregation stages and a couple of aggregation operators in the pipeline. The stages:

• $unwind - This helps to spread the programs array such that I have one document per program, it makes it easy to deal with the nested exercise array inside each program.
• $match - This helps to filter out programs not added by "coach"
• $project - This basically projects the data forward while applying transformation using aggregation operators to some of the input data. I used the $map operator on "program.exercises" to gain access to each exercise in the exercises array. In each exercise object, I used the $filter operator on the nested exercises array to remove exercises whose exId is not "pushup".
• $group - This is just a clean up for the $unwind I did earlier, it groups back the programs that I spread at the beginning of the pipeline.

You can test it out in this playground