Ok , im new in this mongodb world an i have some troubles with it.

i have 3 collections , products , comments y users. products store an array of objectid from comments , an comments store the user_id.

this is the product document example.

{
  "_id": {
    "$oid": "625f1825fd0569646907dbb2"
  },
  "id": {
    "$numberInt": "1"
  },
  "title": "Fjallraven - Foldsack No. 1 Backpack, Fits 15 Laptops",
  "price": {
    "$numberDouble": "109.95"
  },
  "description": "Your perfect pack for everyday use and walks in the forest. Stash your laptop (up to 15 inches) in the padded sleeve, your everyday",
  "category": "men's clothing",
  "image": "https://fakestoreapi.com/img/81fPKd-2AYL._AC_SL1500_.jpg",
  "rating": {
    "rate": {
      "$numberDouble": "3.9"
    },
    "count": {
      "$numberInt": "120"
    }
  },
  "comments": [
    {
      "id": {
        "$oid": "62607a918aaf3e14a2cd1f0e"
      }
    }
  ]
}

this is the comments example

{
  "_id": {
    "$oid": "62607a918aaf3e14a2cd1f0e"
  },
  "user_id": {
    "$oid": "625f1ae4b0e88cd486276a54"
  },
  "comment": "asdjfaslkdf",
  "product_id": {
    "$oid": "625f1825fd0569646907dbb2"
  },
  "rating": {
    "rate": {
      "$numberInt": "0"
    },
    "votes": {
      "$numberInt": "0"
    }
  }
}

And this is the user example.

{
  "_id": {
    "$oid": "625f1ae4b0e88cd486276a54"
  },
  "name": "Ursula",
  "username": "lahee",
  "create_at": {
    "$date": {
      "$numberLong": "1650399972095"
    }
  },
  "password": "$2a$10$Av3HTLljhDkDxoVEyRKGwe4M42A4HHZMWcCfThdOQHbmA3H5EGsny",
  "email": "[email protected]",
  "phone": "102394523",
  "preferences": [],
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYyNWYxYWU0YjBlODhjZDQ4NjI3NmE1NCIsInVzZXJuYW1lIjoibGFoZWUiLCJpYXQiOjE2NTA0ODMzMzEwODUsImV4cCI6MTY1MDQ4MzMzNDY4NX0.UXV7I-1XBZ3T_Y0aPBPdP-gdkPMqvM6dEnZcHXg5_5g",
  "role": "user",
  "__v": {
    "$numberInt": "0"
  }
}

with this documents i want to get all product with comments and user name and email like this:

{
  "_id": {
    "$oid": "625f1825fd0569646907dbb2"
  },
  "price": 109.95,
  "description": "Your perfect pack for everyday use and walks in the forest. Stash your laptop (up to 15 inches) in the padded sleeve, your everyday",
  "comments": [
    {
      "_id": {
        "$oid": "62607a918aaf3e14a2cd1f0e"
      },
      "comment": "asdjfaslkdf",
      "user": [
        {
          "_id": {
            "$oid": "625f1ae4b0e88cd486276a54"
          },
          "name": "Ursula",
          "email": "[email protected]"
        }
      ]
    }
  ]
}

i tried with some rules and querys , the best give me the same user for all comments.

db.products.aggregate([
        {
            $lookup: {
                from: "comments",
                let: { comment_id: "$comments.id" },
                pipeline: [
                    { $match: { $expr:{ $eq:[ "$_id", "$$comment_id" ] } } },
                    {
                        $lookup: {
                            from: "users",
                            localField: "user_id",
                            foreignField: "_id",
                            as: "user"
                        }
                    },
                    
                ],
                as: "comments"
            }
        },
        {
            $project:{
                _id:1,
                name:1,
                price:1,
                description:1,
                comments:{
                    _id:1,
                    comment:1,
                    date_at:1,
                    rate:1,
                    user:{
                        _id:1,
                        name:1,
                        email:1
                    },

                },
            }
        }
    ])

anyone can help me with this problem , i want understand better the pipelines an mongodb querys .

Thanks.

CodePudding user response：

Here's one way you could get your desired output.

db.products.aggregate([
  { // may be multiple comments (or none!)
    "$unwind": {
      "path": "$comments",
      "preserveNullAndEmptyArrays": true
    }
  },
  { // two-stage lookup
    "$lookup": {
      "from": "comments",
      "localField": "comments.id",
      "foreignField": "_id",
      "pipeline": [
        { // get user info
          "$lookup": {
            "from": "users",
            "localField": "user_id",
            "foreignField": "_id",
            "pipeline": [
              {
                "$project": {
                  "name": 1,
                  "email": 1
                }
              }
            ],
            "as": "users"
          }
        },
        {
          "$project": {
            "comment": 1,
            "users": 1
          }
        }
      ],
      "as": "comments"
    }
  },
  { // only fields needed
    "$project": {
      "comments": 1,
      "description": 1,
      "price": 1
    }
  },
  { // groups all comments together
    "$group": {
      "_id": "$_id",
      "description": { "$first": "$description" },
      "price": { "$first": "$price" },
      "comments": { "$push": { "$first": "$comments" } }
    }
  }
])

Try it on mongoplayground.net.