Lookup and combine data in a specific format with mongodb-CodePudding

What is the best way to perform a lookup and combine it with the object that is used for the lookup?

For example, I have collection 1 that is the following format:

{
  _id:ObjectID("xxxxxxxx"),
  id: "12345",
  name: "name xyz",
  goal: "goal 123",
  start: null,
  end: null,
  plan:[
    {
      day: 0,
      exercises:[
        {
          id: "xxxx1",
          stat1: "stat 1 stuff",
          stat2: "stat 2 stuff"
        },
        {
          id: "xxxx2",
          stat1: "stat 1 stuff",
          stat2: "stat 2 stuff"
        }
      ]
    },
    {
      day: 1,
      exercises:[
        {
          id: "xxxx2",
          stat1: "stat 1 stuff",
          stat2: "stat 2 stuff"
        }
      ]
    }
  ]
}

and I have collection 2 (exercises) which is the following format:

{
  _id: ObjectID("yyyyyy"),
  id: "xxxx1",
  stat3: "stat 3 stuff",
  stat4: "stat 4 stuff"
},
{
  _id: ObjectID("yyyyyy"),
  id: "xxxx2",
  stat3: "stat 3 stuff",
  stat4: "stat 4 stuff"
}

Is there a way to perform a lookup and get results in the following format:

{
  _id:ObjectID("xxxxxxxx"),
  id: "12345",
  name: "name xyz",
  goal: "goal 123",
  start: null,
  end: null,
  plan:[
    {
      day: 0,
      exercises:[
        {
          id: "xxxx1",
          stat1: "stat 1 stuff",
          stat2: "stat 2 stuff",
          stat3: "stat 3 stuff",
          stat4: "stat 4 stuff"
        },
        {
          id: "xxxx2",
          stat1: "stat 1 stuff",
          stat2: "stat 2 stuff",
          stat3: "stat 3 stuff",
          stat4: "stat 4 stuff"
        }
      ]
    },
    {
      day: 1,
      exercises:[
        {
          id: "xxxx2",
          stat1: "stat 1 stuff",
          stat2: "stat 2 stuff",
          stat3: "stat 3 stuff",
          stat4: "stat 4 stuff"
        }
      ]
    }
  ]
}

CodePudding user response：

Here's one way you could do it.

db.clients.aggregate([
  {  // lookup from exercises with matching id's
    "$lookup": {
      "from": "exercises",
      "localField": "plan.exercises.id",
      "foreignField": "id",
      "pipeline": [ { "$unset": "_id" } ],  // don't need _id
      "as": "exLookup"
    }
  },
  {  // extend plan with info from lookup
    "$set": {
      "plan": {
        "$map": {  // cycle through each plan array element
          "input": "$plan",
          "as": "planElem",
          "in": {
            "$mergeObjects": [  // keep fields and merge elements
              "$$planElem",  // keep current array element ...
              {              // and merge new fields
                "exercises": {
                  "$map": {  // cycle through each exercises array element
                    "input": "$$planElem.exercises",
                    "as": "exElem",
                    "in": {
                      "$mergeObjects": [  // keep fields and merge elements
                        "$$exElem",  // current array element
                        {
                          "$arrayElemAt": [  // get correct array element ...
                            "$exLookup",     // from lookup
                            { "$indexOfArray": [ "$exLookup.id", "$$exElem.id" ] }
                          ]
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  { "$unset": "exLookup" }  // don't need this anymore
])

Try it on mongoplayground.net.