I want to use $lookup in the aggregate method. The collection I want to do this is like this. It is an array. It has nested objects in the array.

Please notice my problem is with the lookup and the way I want to get my data. The names of the properties are not so important, the important property is "position_id".

[
  {
    name : 'projectName1',
    positions : [
      {
        position_id : id1,
        use : true,
        wage : 0,
        default : true
      },
      {
        position_id : id2,
        use : true,
        wage : 0,
        default : true
      }          
    ]
  }      
]

When I lookup on the "position_id" like this:

$lookup: {
  from: 'positions',
  localField: 'positions.position_id',
  foreignField: '_id',        
  as: 'positions.positionDetails'
}

The result is:

"positions": {
    "positionDetails": [
        {
            "_id": "63d78e5096109914dc963431",
            "field": "electrical",
            "regularName": "elc",
        },
        {
            "_id": "63d78e5096109914dc963433",
            "field": "mechanic",
            "regularName": "mec",
        }
    ]
}

The positions array was changed to an object.

But I want to get data like this :

"positions": [
    {
        "position_id": "63d78e5096109914dc963431",
        "use": true,
        "default": true,
        "wage": 0,
        "positionDetails" : {
            "field": "electrical",
            "regularName": "elc",
        }
    },
    {
        "position_id": "63d78e5096109914dc963433",
        "use": true,
        "default": false,
        "wage": 0,
        "positionDetails" : {
            "field": "mechanic",
            "regularName": "mec",
        }
    }
]

CodePudding user response：

1. $lookup - Join with positions collection for the document into positionDetails field.

2. $set - Set positions field.

   2.1. $map - Iterate each document in positions array and return a new array.

   2.1.1. $mergeObjects - Merge the current iterate document from the positions array with the result from 2.1.1.1.

   2.1.1.1. $first - Get the first matched element from the result 2.1.1.1.1 and assign it to the positionDetails field in the new document.

   2.1.1.1.1. $filter - Filter the elements from the positionDetails array which match the id.

3. $unset - Remove positionDetails array field.

db.doc.aggregate([
  {
    $lookup: {
      from: "positions",
      localField: "positions.position_id",
      foreignField: "_id",
      as: "positionDetails"
    }
  },
  {
    $set: {
      positions: {
        $map: {
          input: "$positions",
          as: "pos",
          in: {
            $mergeObjects: [
              "$$pos",
              {
                "positionDetails": {
                  $first: {
                    $filter: {
                      input: "$positionDetails",
                      cond: {
                        $eq: [
                          "$$pos.position_id",
                          "$$this._id"
                        ]
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: "positionDetails"
  }
])

Demo @ Mongo Playground