Question 117324 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – 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"
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search