MongoDB $lookup on array of documents containing object id

user_7804714 0
Viewed 57

I want to make aggregation pipeline in MongoDB v4.4.

I have two collections.

  1. users collection

    [
  { _id: "user1", username: "john" },
  { _id: "user2", username: "bob" },
]

  2. articles collection

    [
  {
    _id: "article1",
    view_history: [
      { user: "user1", viewed_at: ISODate("...") },
      { user: "user2", viewed_at: ISODate("...") },
      { user: "user1", viewed_at: ISODate("...") },
    ]
  }
]

My desired result after running aggregation on articles collection is:

[
  {
    _id: "article1",
    view_history: [
      { user: "john", viewed_at: ISODate("...") },
      { user: "bob", viewed_at: ISODate("...") },
      { user: "john", viewed_at: ISODate("...") },
    ]
  }
]

So user fields on each history item in view_history are substituted with the user's username.

I tried many answers on SO, but can't find any solution for my situation. Here's what I got so far:

db.articles.aggregate([
  {
    $lookup: {
      from: "users",
      localField: "view_history.user",
      foreignField: "_id",
      as: "output",
    }
  }
])

Which yields:

[
  {
    _id: "article1",
    view_history: [
      { user: "user1", viewed_at: ISODate("...") },
      { user: "user2", viewed_at: ISODate("...") },
      { user: "user1", viewed_at: ISODate("...") },
    ],
    output: [
      { _id: "user1", username: "john" },
      { _id: "user2", username: "bob" },
    ]
  }
]

(There's only one user1 in output, and viewed_at not included)

mongodb
1 Answers
  • $unwind deconstruct view_history array
  • $lookup with users collection
  • get first element from view_history.user.username because it will return array using $arrayElemAt
  • $group by _id and re-construct view_history array
db.articles.aggregate([
  { $unwind: "$view_history" },
  {
    $lookup: {
      from: "users",
      localField: "view_history.user",
      foreignField: "_id",
      as: "view_history.user"
    }
  },
  {
    $addFields: {
      "view_history.user": {
        $arrayElemAt: ["$view_history.user.username", 0]
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      view_history: {
        $push: "$view_history"
      }
    }
  }
])

Playground

Second approach:

  • $lookup with users collection
  • $map to iterate loop of view_history array
  • $filter to iterate loop of output array and match user field
  • $let declare variable u for above filter, in to get first element from filtered result using $arrayElemAt
db.articles.aggregate([
  {
    $lookup: {
      from: "users",
      localField: "view_history.user",
      foreignField: "_id",
      as: "output"
    }
  },
  {
    $project: {
      view_history: {
        $map: {
          input: "$view_history",
          as: "v",
          in: {
            user: {
              $let: {
                vars: {
                  u: {
                    $filter: {
                      input: "$output",
                      cond: { $eq: ["$$this._id", "$$v.user"] }
                    }
                  }
                },
                in: { $arrayElemAt: ["$$u.username", 0] }
              }
            },
            viewed_at: "$$v.viewed_at"
          }
        }
      }
    }
  }
])

Playground

user_89871280
Related