CODEWITHSUNDEEP
sqlnode.jsmongodbnodes
shivlal kumavat
shivlal kumavat

Reputation: 888

Get data from two collection with specific data Mongo

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

Upvotes: 0

Views: 51

Answers (2)

shivlal kumavat
shivlal kumavat

Reputation: 888

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

Upvotes: 0

J.F.
J.F.

Reputation: 15187

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

Upvotes: 1

Related Questions