Find data of my friends and I to sort them using MongoDB

I need to search for information about my friends. I would also like to include my information from my ActorId, so that in descending order my friends and I have the most Fame.

I’ve struggled to build this query, and it’s not optimised. I’m looking for a way to do it.
The first $lookup for friends goes off without a hitch, but it’s the rest that’s a bit tricky. The second lookup user searches for the information of the requesting user (me) for each result of the preceding $lookup. This is done more than once, which is unnecessary.

Here the $lookup stage of user.

Is there a way of partitioning the pipeline to separate the elements? I tried with $group, but it didn’t work.

Here’s my query:

const pipeline = ([
  {
    $match: {
      $or: [
        { RequesterId: ActorId },
        { ReceiverId: ActorId }
      ],
      Status: 1
    }
  },
  {
    $lookup: {
      from: "users",
      let: {
        actorId: {
          $cond: [{
            $eq: [ "$RequesterId", ActorId ]
          }, "$ReceiverId", "$RequesterId"]
        }
      },
      pipeline: [{
        $match: {
          $expr: {
            $or: [{
              $eq: [ "$$actorId", "$ActorId" ]
            }]
          }
        }
      }, {
        $addFields: {
          RoomLikes: {
            $size: "$Room.RoomActorLikes"
          }
        }
      }, {
        $project: {
          _id: 0,
          ActorId: "$ActorId",
          Name: "$Name",
          Money: "$Progression.Money",
          Fame: "$Progression.Fame",
          Fortune: "$Progression.Fortune",
          IsExtra: "$Extra.IsExtra",
          RoomLikes: "$RoomLikes"
        }
      }],
      as: "friends"
    }
  },
  {
    $lookup: {
      from: "users",
      pipeline: [{
        $match: {
          ActorId: 2
        }
      }, {
        $addFields: {
          RoomLikes: {
            $size: "$Room.RoomActorLikes"
          }
        }
      }, {
        $project: {
          _id: 0,
          ActorId: "$ActorId",
          Name: "$Name",
          Money: "$Progression.Money",
          Fame: "$Progression.Fame",
          Fortune: "$Progression.Fortune",
          IsExtra: "$Extra.IsExtra",
          RoomLikes: "$RoomLikes"
        }
      }],
      as: "user"
    }
  },
  {
    $project: {
      newArray: {
        $concatArrays: [ "$user", "$friends" ]
      }
    }
  },
  {
    $unwind: "$newArray"
  },
  {
    $replaceRoot: {
      newRoot: "$newArray",
    }
  },
  {
    $group: {
      _id: "$ActorId",
      ActorId: { $first: "$ActorId" },
      Name: { $first: "$Name" },
      Money: {$first: "$Money"  },
      Fame: { $first: "$Fame" },
      Fortune: { $first: "$Fortune" },
      IsExtra: { $first: "$IsExtra" },
      RoomLikes: { $first: "$RoomLikes" }
    }
  },
  {
    $match: {
      ActorId: { $ne: 1 }, // Remove MSPRetro account from the result
      IsExtra: 0 // Remove deleted users from the result
    }
  },
  { $sort: { Fame: -1 } },
  { $skip: request.pageindex * 7 },
  { $limit: 7 }
]);

Thanks in advance for your help!

>Solution :

You want the $unionWith stage, something along the lines of:

db.friends.aggregate([
  {
    "$match": {
      "$or": [
        {
          "RequesterId": 2
        },
        {
          "ReceiverId": 2
        }
      ],
      "Status": 1
    }
  },
  {
    "$lookup": {
      "from": "users",
      "let": {
        "actorId": {
          "$cond": [
            {
              "$eq": [
                "$RequesterId",
                2
              ]
            },
            "$ReceiverId",
            "$RequesterId"
          ]
        }
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$or": [
                {
                  "$eq": [
                    "$$actorId",
                    "$ActorId"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "friends"
    }
  },
  {
    "$unwind": "$friends"
  },
  {
    "$replaceRoot": {
      "newRoot": "$friends"
    }
  },
  {
    "$unionWith": {
      coll: "users",
      "pipeline": [
        {
          "$match": {
            "ActorId": 2
          }
        }
      ]
    }
  },
  {
    "$match": {
      "ActorId": {
        "$ne": 1
      },
      "Extra.IsExtra": 0
    }
  },
  {
    "$sort": {
      "Fame": -1
    }
  },
  {
    "$skip": 0
  },
  {
    "$limit": 7
  },
  {
    "$project": {
      "_id": 0,
      "ActorId": "$ActorId",
      "Name": "$Name",
      "Money": "$Progression.Money",
      "Fame": "$Progression.Fame",
      "Fortune": "$Progression.Fortune",
      "IsExtra": "$Extra.IsExtra",
      "RoomLikes": {
        "$size": "$Room.RoomActorLikes"
      }
    }
  }
])

Demo playground

I’ve also changed a few other things here, notably:

• Got rid of the $addFields stage as that logic can happen directly in the $project.
• Collapsed the two $projects into a single one, and moved it to the very last stage of the pipeline.
• Updated the "Extra.IsExtra" field name in the $match since I pulled it ahead of the $project.
• Get rid of the $group (which I think was only present due to your duplication with the initial dual $lookup approach. This removes one blocking stage, though you’ll still have another for that $sort.

I would personally also look to include that $match logic earlier in the pipeline (e.g. in the $lookup and $unionWith directly) but I don’t know the data well enough to say whether it makes much of a difference.