Get data from two collection from first collection(test1) all data and from second collection (test2) customer name using createdBy and updatedBy from test1 collection.

In createdBy and updatedBy I want fullname from test2 collection:

Test1 collection:

{
  "_id": "kcXtyaB7jGPw9Ks",
  "dateCreated": "2022-07-12T13:09:16.270Z",
  "dateModified": "2022-07-12T13:09:16.270Z",
  "data1": 1,
  "data2": 100,
  "data3": 5,
  "createdBy": "xQQrzRgi8",
  "updatedBy": "56sgAeKfx"
}

Test2 collection:

{
  "_id": "xQQrzRgi8",
  "fullName": "test name created"
},
{
  "_id": "56sgAeKfx",
  "fullName": "test name update"
}

Response be like:

{
 "_id": "kcXtyaB7jGPw9Ks",
  "dateCreated": "2022-07-12T13:09:16.270Z",
  "dateModified": "2022-07-12T13:09:16.270Z",
  "data1": 1,
  "data2": 100,
  "data3": 5,
  "createdBy": "test name created",
  "updatedBy": "test name update"
}

CodePudding user response：

If I've understood correctly, you can use $lookup like this:

This query do a "join" between "Test1" and "Test2" using updatedBy and _id fields.

And after that get the first element in the result (I assume there were only one element because you are comparing with _id but if there is more than one you can use another way like $unwind) to output the value.

Edit: To get both values (created and updated) you can do a second $lookup.

Now the query:

• Get the updatedBy name from field _id in Test2.
• Set value into field updatedBy.
• Get the createdBy name from field _id in Test2.
• Set value into field createdBy.
• Use $project to not output result.

db.Test1.aggregate([
  {
    "$lookup": {
      "from": "Test2",
      "localField": "updatedBy",
      "foreignField": "_id",
      "as": "result"
    }
  },
  {
    "$set": {
      "updatedBy": {
        "$first": "$result.fullName"
      }
    }
  },
  {
    "$lookup": {
      "from": "Test2",
      "localField": "createdBy",
      "foreignField": "_id",
      "as": "result"
    }
  },
  {
    "$set": {
      "createdBy": {
        "$first": "$result.fullName"
      }
    }
  },
  {
    "$project": {
      "result": 0
    }
  }
])

Example here

CodePudding user response：

I solved my query with below mongo query:

db.Test1.aggregate([
  {
    $lookup: {
      from: "Test2",
      localField: "updatedBy",
      foreignField: "_id",
      as: "updatedByName",
    },
  },
  {
    $lookup: {
      from: "Test2",
      localField: "createdBy",
      foreignField: "_id",
      as: "createdByName",
    },
  },
  {
    $set: {
      updatedBy: {
        $first: "$updatedByName.fullName",
      },
      
    },
  },
  {
    $set: {
      createdBy: {
        $first: "$createdByName.fullName",
      },
    },
  },
  {
    $project: {
      updatedByName: 0,
      createdByName: 0,   
    }, 
  }
])

Here is Solved query https://mongoplayground.net/p/7Ekh-q8tkTy