I have three collections:

Collection trainingPlan:

        {
            "_id": "622683098c0a837e9d8e62ca",
            
            "days": [
                {
                    "rest": false,
                    "training_id": "622662616c279faf3e0e00bd",
                    "_id": "622683098c0a837e9d8e62cb"
                },
                {
                    "rest": true,
                    "_id": "622683098c0a837e9d8e62cc"
                },
                {
                    "rest": false,
                    "training_id": "6226649f6c279faf3e0e00cc",
                    "_id": "622683098c0a837e9d8e62cd"
                },
                {
                    "rest": true,
                    "_id": "622683098c0a837e9d8e62ce"
                }
            ],
            "__v": 0
        }

For every day, i store training_id as foreign key which points to training collection:

        {
            "_id": "622662616c279faf3e0e00bd",
            "name": "Core easy",
            "exerc": [
                {
                    "ex_id": "62265892d68c687412db2423",
                    "sets":4,
                    
                    "_id": "622662616c279faf3e0e00be"
                },
                {
                    "ex_id": "622655f383c6328b70380f72",
                    "sets":4,
                    
                    "_id": "622662616c279faf3e0e00c2"
                },
                {
                    "ex_id": "62265bb4b8a1af9d8532c7fd",
                   "sets":4,
                    "_id": "622662616c279faf3e0e00c7"
                }
            ],
            "__v": 0
        },
        {
            "_id": "6226649f6c279faf3e0e00cc",
            "name": "Core medium",
            "exerc": [
                {
                    "ex_id": "62265cd3b8a1af9d8532c802",
                    "sets":4,
                    "_id": "6226649f6c279faf3e0e00cd"
                },
                {
                    "ex_id": "62265bb4b8a1af9d8532c7fd",
                    "sets":4,
                    "_id": "6226649f6c279faf3e0e00d2"
                },
                {
                    "ex_id": "62265892d68c687412db2423",
                    "sets":4,
                    "_id": "6226649f6c279faf3e0e00d7"
                }
            ],
            "__v": 0
        }
    

Every training have array exec which contains reference to exercises collection:

    {
        "_id": "6226526a96b270821aa418bd",
        "name": "Leg killer",
        "muscle": [
            "kvadriceps"
        ],
        "__v": 0
    },
    {
        "_id": "622655f383c6328b70380f72",
        "name": "Bulgarian squat",
        "muscle": [
            "kvadriceps",
            "zadnja loža",
            "gluteus"
        ],
        "__v": 0
    },
    {
        "_id": "62265892d68c687412db2423",
        "name": "Plank",
        "muscle": [
            "core",
            "trbušnjaci"
        ],
        "__v": 0
    },
    
    {
        "_id": "62265bb4b8a1af9d8532c7fd",
        "name": "Leg Raise",
        "muscle": [
            "core",
            "trbušnjaci"
        ],
        "linkVideo": "https://www.youtube.com/watch?v=l4kQd9eWclE",
        "__v": 0
    },
    
    {
        "_id": "62265cd3b8a1af9d8532c802",
        "name": "Sit up",
        "muscle": [
            "core",
            "trbušnjaci"
        ],
        "linkVideo": "https://www.youtube.com/watch?v=1fbU_MkV7NE",
        "__v": 0
    }

I have tried aggregation, nested lookups (which doesn't suite me because i have to use unwind after first lookup), addField with map but it wont work. So can anyone help me or recommend me in which way i should go. I have found similar problem $lookup with foreign field of array & nested foreign field but it doesnt have answers too.

My desired result should be:

{
            "_id": "622683098c0a837e9d8e62ca",
            
            "days": [
                {
                    "rest": false,
                    "training_id": "622662616c279faf3e0e00bd",
                    "_id": "622683098c0a837e9d8e62cb",
                    "name": "Core easy",
                    "exerc": [
                    {
                        "ex_id": "62265892d68c687412db2423",
                        "name": "Plank",
                        "muscle": [
                        "core",
                        "trbušnjaci"
                         ],
                        "sets":4,
                        
                        "_id": "622662616c279faf3e0e00be"
                    },
                    {
                    "ex_id": "622655f383c6328b70380f72",
                     
        
                     "name": "Bulgarian squat",
                     "muscle": [
                        "kvadriceps",
                        "zadnja loža",
                        "gluteus"
                      ],
              
    
                    "sets":4,
                    
                    "_id": "622662616c279faf3e0e00c2"
                    }
                    ]
                },
                  

                  and so on...
                   
                
        }

CodePudding user response：

I think this does what you want, and hopefully there's an easier way. I think a puzzle piece you hadn't considered yet was "$mergeObjects".

db.trainingPlan.aggregate([
  { "$unwind": "$days" },
  { "$lookup": {
      "from": "training",
      "localField": "days.training_id",
      "foreignField": "_id",
      "as": "training"
    }
  },
  { "$unwind": "$training" },
  { "$unwind": "$training.exerc" },
  { "$set": {
      "days": {
        "$mergeObjects": [ "$training", "$days" ]
      }
    }
  },
  { "$lookup": {
      "from": "exec",
      "localField": "days.exerc.ex_id",
      "foreignField": "_id",
      "as": "exec"
    }
  },
  { "$set": {
      "days.exerc": {
        "$mergeObjects": [ { "$first": "$exec" }, "$days.exerc" ]
      }
    }
  },
  { "$unset": [ "__v", "days.__v", "days.exerc.__v" ] },
  { "$group": {
      "_id": "$_id",
      "days": { "$push": "$days" }
    }
  }
])

Try it on mongoplayground.net.