MongoDB $lookup replace a user_id field in object(the object will be in a array of objects) with a u-CodePudding

I have a collection of tickets the ticket has this scheme:

{
    "_id": {
        "$oid": "61bc5ea14bde4bfd53e824c8"
    },
    "number": {
        "$numberLong": "1"
    },
    "creation_time": {
        "$date": "2021-12-17T09:55:45.857Z"
    },
    "modified_time": null,
    "title": "Fix ethernet ports",
    "description": null,
    "tags": [],
    "assigned_to": null,
    "created_by": {
        "$oid": "61bc5ea14bde4bfd53e824c7"
    },
    "status": "Opened",
    "solution": null,
    "priority": "Low",
    "confidentiality": "Public",
    "changes": [],
    "messages": [{
        "$oid": "61bc5ea14bde4bfd53e824c6"
    }, {
        "$oid": "61bc931ca1e7188973dec71b"
    }, {
        "$oid": "61bf3ef02f689cef7e2aeb1f"
    }],
    "last_activity": {
        "$date": "2021-12-17T09:55:45.857Z"
    },
    "hidden": false
}

I am able to with $lookup query

 $lookup: {
  from: 'messages',
  localField: 'messages',
  foreignField: '_id',
  as: 'messages'
 }

which retrieves all the messages but now in the messages is field "author" and it contains id to a user document, how can i replace in every message the author id with corresponding user document?

message scheme:

{
    "_id": {
        "$oid": "61bc5ea14bde4bfd53e824c6"
    },
    "time": {
        "$date": "2021-12-17T09:55:45.856Z"
    },
    "edited_time": null,
    "changes": [],
    "content": "sdsdsd",
    "author": {
        "$oid": "61bc5ea14bde4bfd53e824c7"
    }
}

user scheme:

{
    "_id": {
        "$oid": "61bc5ea14bde4bfd53e824c7"
    },
    "role": "Employee",
    "username": null,
    "first_name": "...",
    "last_name": "...",
    "email": "...",
    "token": "...",
    "solved_tickets": [],
    "reputation": {
        "$numberLong": "0"
    }
}

I tried so many things but it always ended up not working, I am trying to do this for days, I think I am pretty dumb, I must have missed something important, feeling the imposter syndrome pretty hard rn

CodePudding user response：

What you are looking for are nested lookups, inside your lookup you can use the field pipeline to add an entire aggergation to the other document.

db.collection.aggregate([
  {
    '$lookup': {
      'from': 'messages', 
      'localField': 'messages', 
      'foreignField': '_id', 
      'as': 'messages', 
      'pipeline': [
        {
          '$lookup': {
            'from': 'user', 
            'localField': 'author', 
            'foreignField': '_id', 
            'as': 'author'
          }
        }
      ]
    }
  }
])