MongoDB - Aggregate on nested array of objects

Question

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",
        }
    }
]

Yong Shun · Accepted Answer · 2023-01-31 05:54:59Z

1

$lookup - Join with positions collection for the document into positionDetails field.
$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.
$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

edited Jan 31, 2023 at 5:54

answered Jan 31, 2023 at 0:30

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mehdi M Over a year ago

Thank you so much @Yong Shun . it works fine. how can I be that much expert in aggregation? I tried many ways before your answer..., I should learn more about these operators that you have used in the code.

Yong Shun Over a year ago

You are mostly welcome. I also still have a long journey to be an expert. More practice and raising questions if any is the best way to improve. =D And do feel free to read StackOverflow questions & answers, there are a lot of experts to assist and give you a learning boost.

Collectives™ on Stack Overflow

MongoDB - Aggregate on nested array of objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related