Join like in mongodb with arrays of documents and conditions

Question

I am struggling to create a MongoDB query to use aggregation in order to perform a join like a query with conditions. This is my input :

'Things' collection :

{
    _id : 1
    users : [
        {
            userId : 00001,
            role : "creator"
        },
        {
            userId : 00002,
            role : "spectator"
        }
    ]
}
{
    _id : 2
    users : [
        {
            userId : 00002,
            role : "creator"
        },
        {
            userId : 00001,
            role : "spectator"
        }
    ]
}

'Users' Collection :

{
    _id : 00001,
    name : "John"
}
{
    _id : 00002,
    name : "Doe"
}

I want to get the creator's name/informations for a given 'Thing' _id and get something like this for _id = 1:

{
    _id : 00001,
    name : "John"
}

This is where I've been so far :

db.Things.aggregate([
    {
        $match:{
            _id:1
        }
    },
    {
        $lookup:{
            from:"Users",
            localField:"Users.userId",
            foreignField:"_id",
            as:"usersData"
        }
    },
    {
        $unwind : "$usersData"
    }
])

Thanks in advance.

Answer 1

• $match things _id and users role condition
• $unwind deconstruct users array
• $match users role condition
• $lookup with users collection
• $arrayElemAt to get first element from result of lookup
• $replaceRoot to replace above object to root

db.things.aggregate([
  { $match: { _id: 1, "users.role": "creator" } },
  { $unwind: "$users" },
  { $match: { "users.role": "creator" } },
  {
    $lookup: {
      from: "Users",
      localField: "users.userId",
      foreignField: "_id",
      as: "users"
    }
  },
  {
    $replaceRoot: {
      newRoot: { $arrayElemAt: ["$users", 0] }
    }
  }
])

Playground

---

A second approach without using $undind stage,

• $match things _id and users role condition
• $filter to iterate loop of uses array and filter by role creator
• $arrayElemAt to select first element from above filtered result
• $lookup with users collection
• $arrayElemAt to get first element from result of lookup
• $replaceRoot to replace above object to root

db.things.aggregate([
  { $match: { _id: 1, "users.role": "creator" } },
  {
    $addFields: {
      users: {
        $arrayElemAt: [
          {
            $filter: {
              input: "$users",
              cond: { $eq: ["$$this.role", "creator"] }
            }
          },
          0
        ]
      }
    }
  },
  {
    $lookup: {
      from: "Users",
      localField: "users.userId",
      foreignField: "_id",
      as: "users"
    }
  },
  {
    $replaceRoot: {
      newRoot: { $arrayElemAt: ["$users", 0] }
    }
  }
])

Playground