Given following collections. I have to get the title field and combine to identifier

CREDENTIAL:

{
    _id: ..
    title: ..
}

USER_CREDENTIAL:

{
    _id: ..
    credential_id: .. (from credential collection)
    created_at: ..
    identifier: {
        first_name: ..
        middle_name: ..
        last_name: ..
    }
}

The response should be:

{
   user_credential_id: 
   member: {
        first_name:
        middle_name:
        last_name:
        title:
        created_at:
    }
}
    

CodePudding user response：

1. $lookup - Join user and user_credential_id with a pipeline to match the condition ($match) and decorate the document ($project).
2. $unwind - Deconstruct member array to multiple documents.
3. $project - Decorate the output document.

db.user.aggregate([
  {
    "$lookup": {
      "from": "user_credential",
      let: {
        "user_credential_id": "$_id",
        "title": "$title"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$credential_id",
                "$$user_credential_id"
              ]
            }
          }
        },
        {
          $project: {
            first_name: "$identifier.first_name",
            middle_name: "$identifier.middle_name",
            last_name: "$identifier.last_name",
            title: "$$title",
            created_at: "$created_at",
            
          }
        }
      ],
      "as": "member"
    }
  },
  {
    $unwind: "$member"
  },
  {
    $project: {
      _id: 0,
      user_credential_id: "$_id",
      member: 1
    }
  }
])

Sample Mongo Playground

CodePudding user response：

You can try this query

db.user.aggregate([
  {
    $lookup: {
      from: "credentials",
      localField: "credential_id",
      foreignField: "_id",
      as: "user_credential"
    }
  },
  {
    $unwind: "$user_credential"
  },
  {
    $project: {
      _id: 0,
      user_credential_id: "$credential_id",
      member: {
        first_name: "$identifier.first_name",
        middle_name: "$identifier.middle_name",
        last_name: "$identifier.last_name",
        title: "$user_credential.title",
        created_at: "$created_at",
        
      }
    }
  }
])

You can check it out here