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

Lookup giving wrong results in mongodb

I am new in mongodb. I am facing issue while using lookup. I have two documents. users and user_posts

users collection
db.users.find({}).pretty()
{
"_id" : ObjectId("655c3285822c4a52150c30a7"),
"name" : "kunal",
"email" : "[email protected]",
}
{
"_id" : ObjectId("655c3285822c4a52150c30ab"),
"name" : "Amit",
"email" : "[email protected]",
}

Now i have other collection

user_posts collection
db.user_posts.find({}).pretty();
{
"_id" : ObjectId("655c3285822c4a52150c30a8"),
"user_id" : ObjectId("655c3285822c4a52150c30a7"),
"post_name" : "Test Post"
}
{
"_id" : ObjectId("655c3285822c4a52150c30a9"),
"user_id" : ObjectId("655c3285822c4a52150c30a7"),
"post_name" : "Test Post 2"
}
{
"_id" : ObjectId("655c3285822c4a52150c30aa"),
"user_id" : ObjectId("655c3285822c4a52150c30a7"),
"post_name" : "Test Post 3"
}

Now if you see all user_posts belongs to kunal user_id (655c3285822c4a52150c30a7) but when i aggreate the data with lookup its returning me both users. See the below results

> db.user_posts.aggregate([{$lookup: {from: "users",localField: "id",foreignField: "user_id",as: "user_info"}}]).pretty();
        {
          "_id" : ObjectId("655c3285822c4a52150c30a8"),
          "user_id" : ObjectId("655c3285822c4a52150c30a7"),
          "post_name" : "Test Post",
          "updated_at" : ISODate("2023-11-21T04:31:01.232Z"),
          "created_at" : ISODate("2023-11-21T04:31:01.232Z"),
          "user_info" : [
            {
              "_id" : ObjectId("655c3285822c4a52150c30a7"),
              "name" : "kunal",
              "email" : "[email protected]",
            },
            {
              "_id" : ObjectId("655c3285822c4a52150c30ab"),
              "name" : "Amit",
              "email" : "[email protected]",
            }
          ]
        }
        {
          "_id" : ObjectId("655c3285822c4a52150c30a9"),
          "user_id" : ObjectId("655c3285822c4a52150c30a7"),
          "post_name" : "Test Post 2",
          "updated_at" : ISODate("2023-11-21T04:31:01.233Z"),
          "created_at" : ISODate("2023-11-21T04:31:01.233Z"),
          "user_info" : [
            {
              "_id" : ObjectId("655c3285822c4a52150c30a7"),
              "name" : "kunal",
              "email" : "[email protected]",
            },
            {
              "_id" : ObjectId("655c3285822c4a52150c30ab"),
              "name" : "Amit",
              "email" : "[email protected]",
            }
          ]
        }
        {
          "_id" : ObjectId("655c3285822c4a52150c30aa"),
          "user_id" : ObjectId("655c3285822c4a52150c30a7"),
          "post_name" : "Test Post 3",
          "updated_at" : ISODate("2023-11-21T04:31:01.234Z"),
          "created_at" : ISODate("2023-11-21T04:31:01.234Z"),
          "user_info" : [
            {
              "_id" : ObjectId("655c3285822c4a52150c30a7"),
              "name" : "kunal",
              "email" : "[email protected]",
            },
            {
              "_id" : ObjectId("655c3285822c4a52150c30ab"),
              "name" : "Amit",
              "email" : "[email protected]",
            }
          ]
        }

Now if you see both users are coming in user_info object. It should come only kunal user as these posts belongs to that user only why other users also coming?.. What i am doing wrong in the query?

Tags:

2

Answers


  1. 0

    you could write a query as follows

    db.users.aggregate([
{
    $match: { name: "kunal" }
},
{
    $lookup: {
        from: "posts",
        localField: "_id",
        foreignField: "user_id",
        as: "user_posts"
    }
},
{
    $unwind: "$user_posts"
},
{
    $project: {
        _id: "$user_posts._id",
        post_name: "$user_posts.post_name",
        user_id: "$user_posts.user_id"
    }
}])
    Login or Signup to reply.
  2. 0

    I think the problem is with localField and foreignField.

    foreignField:

    This is the field from the target documents.

    So it should be "_id" as the target document is "users"

    localField:

    This is the field from the input documents.

    So it should be "user_id" as the input document is "user_posts"

    The modified code snippet:

     {
    $lookup: {
      from: "users",
      localField: "user_id",
      foreignField: "_id",
      as: "user_info"
    }
  }

    The complete query will be:

      > db.user_posts.aggregate([{
        $lookup: {
          from: "users",
          localField: "user_id",
          foreignField: "_id",
          as: "user_info"
        }
      }
]).pretty();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search