How to lookup an array of objects and still retain the object properties?

user_9953550 0
Viewed 322

Here is the code in mongodb playground.

I have a query that looks like below:

    db.posts.aggregate({
      $lookup: {
        from: "users",
        let: {comments: "$comments"},
        pipeline: [
          {$match: {$expr: {$in: ["$_id", "$$comments.userId"]} }},
          {"$addFields":{"text": "$$comments.text"}}
        ],
        as: "comments"
      }
    })

The problem with this query is that the text value I get is is an array containing the texts from all comments. If I try to filter by userId, I get another problem. If a user has commented more than once the text value will be an array containing all of their comments.

How can I get the looked up user and their comment as in one object?

EDIT

I expect a result that looks like below:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "id": "1",
    "comments": [
      {
        "_id": "u1",
        "name": "James",
        "username": "jamo",
        "text": "Hi there, who are you?"
      },
      {
        "_id": "u1",
        "name": "James",
        "username": "jamo",
        "text": "Hi! Are you still there?"
      }
    ],
  }
]
aggregation-framework mongodb-query mongodb arrays
1 Answers
  • $unwind deconstruct comments array
  • $lookup join users collection,
  • $unwind get object of user
  • $group by _id and reconstruct comments with required fields
db.posts.aggregate([
  { $unwind: "$comments" },
  {
    $lookup: {
      from: "users",
      localField: "comments.userId",
      foreignField: "_id",
      as: "comments.user"
    }
  },
  { $unwind: "$comments.user" },
  {
    $group: {
      _id: "$_id",
      id: { $first: "$id" },
      comments: {
        $push: {
          _id: "$comments._id",
          text: "$comments.text",
          username: "$comments.user.username",
          name: "$comments.user.name"
        }
      }
    }
  }
])

Playground

Second option, without $unwind,

  • $lookup with users collection
  • $map to iterate loop of comments array
  • $filter to iterate look of users array and find matching user info
  • $arrayElemAt return first object from filtered user from $filter
  • $mergeObjects will merge comment object and user info
db.posts.aggregate([
  {
    $lookup: {
      from: "users",
      localField: "comments.userId",
      foreignField: "_id",
      as: "users"
    }
  },
  {
    $project: {
      comments: {
        $map: {
          input: "$comments",
          as: "c",
          in: {
            $mergeObjects: [
              "$$c",
              {
                $arrayElemAt: [
                  {
                    $filter: {
                      input: "$users",
                      cond: { $eq: ["$$c.userId", "$$this._id"] }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Playground

I have not tested performance of both queries, Try and use suitable query

user_89871280
Related