Project from a double nested array by checking a value inside the array Mongodb

Question

{
    _id: 'uniquewId',
    programs: [
        {
            prgName: 'prgOne',
            progress: '5',
            addedBy: 'coach'
            exercises: [
                {
                     date: '1/12/20',
                     exercises: [
                         {
                              exId: 'pushup',
                              progress: 5,
                              attributes: [
                                  {
                                     id: 'myId',
                                     done: true
                                  },
                                  {
                                     id: 'myId2',
                                     done: false
                                  } 
                              ]
                         },
                         {
                              exId: 'situp',
                              progress: 5,
                              attributes: [
                                  {
                                     id: 'myId',
                                     done: true
                                  },
                                  {
                                     id: 'myId2',
                                     done: true
                                  } 
                              ]
                         }
                     ] 
                },
                {
                    date: '2/12/20',
                    exercises: [
                         {
                              exId: 'pushup',
                              progress: 5,
                              attributes: [
                                  {
                                     id: 'myId',
                                     done: true
                                  },
                                  {
                                     id: 'myId2',
                                     done: false
                                  } 
                              ]
                         },
                         {
                              exId: 'situp',
                              progress: 5,
                              attributes: [
                                  {
                                     id: 'myId',
                                     done: fase
                                  },
                                  {
                                     id: 'myId2',
                                     done: false
                                  } 
                              ]
                         }
                     ] 
                }   
            ]
        },
        {
            prgName: 'prgTwo',
            progress: '5',
            addedBy: 'coach2'
            exercises: [
                 {
                     date: '1/12/20',
                     exercises: [
                         {
                              exId: 'pushup',
                              progress: 5,
                              attributes: [
                                  {
                                     id: 'myId',
                                     done: true
                                  },
                                  {
                                     id: 'myId2',
                                     done: true
                                  } 
                              ]
                         },
                         {
                              exId: 'situp',
                              progress: 5,
                              attributes: [
                                  {
                                     id: 'myId',
                                     done: false
                                  },
                                  {
                                     id: 'myId2',
                                     done: false
                                  } 
                              ]
                         }
                     ] 
                },
                {
                    date: '2/12/20',
                    exercises: [
                         {
                              exId: 'pushup',
                              progress: 5,
                              attributes: [
                                  {
                                     id: 'myId',
                                     done: true
                                  },
                                  {
                                     id: 'myId2',
                                     done: false
                                  } 
                              ]
                         },
                         {
                              exId: 'situp',
                              progress: 5,
                              attributes: [
                                  {
                                     id: 'myId',
                                     done: true
                                  },
                                  {
                                     id: 'myId2',
                                     done: false
                                  } 
                              ]
                         }
                     ] 
                }   
            ]
        }
    ]
}

That above is the structure of my document. What i want to do is from the programs array i want to project the name of exercises that have atleat one document inside attribute array which has done key marked as true. What i am trying to do is figure out exercises which has done set to true inside the attributes array
Is there any way i can get the output like:

{
    _id: 'uniquewId',
    programs: [
        {
            prgName: 'prgOne',
            exercises: [
                'pushup',
                'situp'  
            ]  
        },
        {
            prgName: 'prgTwo',
            exercises: [
                'pushup',
                'situp'  
            ]  
        }
    ]
}

Why only single element for prgTwo?

mickl
– mickl

2020-03-29 16:33:23 +00:00
Commented Mar 29, 2020 at 16:33 — mickl
– mickl, Commented Mar 29, 2020 at 16:33
Sorry it shoud have two elements

Akhil S
– Akhil S

2020-03-30 05:31:52 +00:00
Commented Mar 30, 2020 at 5:31 — Akhil S
– Akhil S, Commented Mar 30, 2020 at 5:31

Valijon · Accepted Answer · 2020-03-30 06:36:40Z

It's a tedious process.

With $map operator we can iterate over arrays. Also $map allows transform array of objects into array of strings.

[{k1:v1}, {k2:v2}, ...] -> [v1, v2, ...]

Pseudocode

1st step. We perform complex aggregation and pre-calculate values

for (Programs program: doc.programs) {
    return {
        prgName : program.prgName,
        exercises : [
            for (Exercises exer1: program.exercises) {
                for (Exercises exer2: exer1.exercises) {
                    return {
                        exId : exer2.exId,
                        done : exer2.attributes.done.contains(true)
                    }
                }
            }
        ]
    }
}

2nd step. We construct desired result

db.collection.aggregate([
  {
    $project: {
      programs: {
        $map: {
          input: "$programs",
          as: "program",
          in: {
            prgName: "$$program.prgName",
            exercises: {
              $reduce: {
                input: {
                  $map: {
                    input: "$$program.exercises",
                    as: "exer1",
                    in: {
                      $map: {
                        input: "$$exer1.exercises",
                        as: "exer2",
                        in: {
                          exId: "$$exer2.exId",
                          done: {
                            $in: [
                              true,
                              "$$exer2.attributes.done"
                            ]
                          }
                        }
                      }
                    }
                  }
                },
                initialValue: [],
                in: {
                  $concatArrays: [
                    "$$value",
                    "$$this"
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $project: {
      programs: {
        $map: {
          input: "$programs",
          as: "program",
          in: {
            prgName: "$$program.prgName",
            exercises: {
              $reduce: {
                input: {
                  $filter: {
                    input: "$$program.exercises",
                    cond: {
                      $eq: [
                        "$$this.done",
                        true
                      ]
                    }
                  }
                },
                initialValue: [],
                in: {
                  $concatArrays: [
                    "$$value",
                    {
                      $cond: [
                        {
                          $in: [
                            "$$this.exId",
                            "$$value"
                          ]
                        },
                        [],
                        [
                          "$$this.exId"
                        ]
                      ]
                    }
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
])

MongoPlayground

Thanks for the reply but there was a mistake in my question. I have updated the question. Sorry :)
Thanks it works :) . Is there any way it can be done using a single projection. Just asking out of curiosity

Collectives™ on Stack Overflow

Project from a double nested array by checking a value inside the array Mongodb

1 Answer 1

Pseudocode

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Pseudocode

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related