How to do mongodb $lookup, but keep other fields-CodePudding

Each document in my collection has a list of people and their status:

[
  {
    _id: 61c005f839d7239960dced76,
    people: [
      { id: 61c005b939d7239960dceca5, status: 'pending' },
      { id: 6204232e0aa660002e2e2617, status: 'active' }
    ]
  },
  // ...
]

I do a lookup to replace the person ID with the actual data from another collection, with this lookup stage:

{                                                                 
  $lookup: {                                                      
    from: 'user',                                                 
    let: { people: '$people' },                                   
    pipeline: [ 
      { $match: { $expr: { $in: [ '$_id', '$$people.id' ] } } }
    ],                                                            
    as: 'people'                                                 
  }                                                               
}

The result of this is something like this:

[
  { 
    _id: 61c005f839d7239960dced76,
    people: [
      {
        _id: 61c005b939d7239960dceca5,
        lastName: 'Joe',
        firstName: 'Shmo',
      },
      {
        _id: 6204232e0aa660002e2e2617,
        firstName: 'John',
        lastName: 'Appleseed',
      }
    ]
  },
  // ...
]

That's great, but I've lost the "status" field from the original documents. Is there a way to keep the status field in the result? Or copy it in afterward?

CodePudding user response：

It would be better to move status to the people collection but if you want to keep it and you are fine with having following structure

"people": {
   "data": {
       "_id": 100,
       "lastName": "...",
       "firstName": "..."
   },
   "status": "active"
}

I can suggest using $unwind

db.user.aggregate([
  {
    "$unwind": "$people"
  },
  {
    "$lookup": {
      "from": "people",
      "localField": "people.id",
      "foreignField": "_id",
      "as": "people.data"
    }
  },
  {
    "$project": { // this one is used to remove duplicate id, can be skipped
      "people.data": 1,
      "people.status": 1
    }
  },
  {
    "$unwind": "$people.data"
  },
]);

CodePudding user response：

Try this: https://mongoplayground.net/p/gRLzpJ3i6Ir

Not very efficient...

db.coll.aggregate([
  {
    $lookup: {
      from: "user",
      let: {
        people: "$people"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$people.id"
              ]
            }
          }
        }
      ],
      as: "people2"
    }
  },
  {
    "$addFields": {
      "people": {
        $map: {
          input: "$people2",
          in: {
            "$mergeObjects": [
              "$$this",
              {
                "$arrayElemAt": [
                  {
                    "$filter": {
                      "input": "$people",
                      "as": "pstatus",
                      "cond": {
                        $eq: [
                          "$$pstatus.id",
                          "$$this._id"
                        ]
                      }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  }
])