Alternative solution to `$lookup` needed because the collection in the `from` field is sharded

Question

Query with arbitrary number of filter conditions that come from querying the same collection

I am referring to the question above.

Here is an additional requirement:

The score table is sharded. Hence, it can no longer be in the $lookup stage.

Is there an alternative solution that also only makes one trip to the MongoDB API?

Answer 1

One way to do it without lookup is using $group, for example:

db.score.aggregate([
  {
    $group: {
      _id: "$test_id",
      highestScore: {$max: "$score"},
      results: {
        $push: {score: "$score", "tester_id": "$tester_id"}
      },
      ourTester: {
        $push: {score: "$score", "tester_id": "$tester_id"}
      }
    }
  },
  {$match: {"ourTester.tester_id": userId}},
  {
    $project: {
      ourTester: {
        $filter: {
          input: "$ourTester",
          as: "item",
          cond: {$eq: ["$$item.tester_id", userId]}
        }
      },
      results: {
        $filter: {
          input: "$results",
          as: "item",
          cond: {$eq: ["$$item.score", "$highestScore"]}}
      }
    }
  },
  {
    $project: {
      ourTester: {"$arrayElemAt": ["$ourTester", 0]},
      highest: {"$arrayElemAt": ["$results", 0]}
    }
  },
  {
    $match: {
      $expr: {$gt: ["$highest.score", "$ourTester.score"]}
    }
  },
  {
    $project: {
      score: "$highest.score",
      tester_id: "$highest.tester_id",
      test_id: "$res._id"
    }
  }
])

As you can see here