My goal is to find two random documents. Each document has a rating. The rating difference between these two document doesn't matter to me. But what matters is the "distance in documents" between these two documents.

Take this list for example. Say the aggregation randomly selects Person7. Now I want a random document say max 3 documents away from Person7. So in this case it should be either Person4, 5, 6, 8, 9, 10.

    [
     { name: "Person1", rating: 50 },
     { name: "Person2", rating: 55 },
     { name: "Person3", rating: 60 },
     { name: "Person4", rating: 65 },
     { name: "Person5", rating: 70 },
     { name: "Person6", rating: 75 },
     { name: "Person7", rating: 800 },
     { name: "Person8", rating: 850 },
     { name: "Person9", rating: 900 },
     { name: "Person10", rating: 100000 },
     { name: "Person11", rating: 100001 },
     { name: "Person12", rating: 102000 }
    ]

And in the end I would like to end up with both of these randomly selected documentes as a result of the pipeline. So for example "Person2" and "Person5" or "Person8" and "Person11", but not "Person1" and "Person5" since the distance between them is beyond 3.

I think I would have to aggregate this list but I'm not sure what type of aggregations to run. Also I'm not sure one aggregation is enough. I would maybe need to two this in multiple steps(in a transaction).

CodePudding user response：

Starting from MongoDB v5.0 , you can use $setWindowFields to get the nearby documents. You can then use $sample to randomly get a one document.

db.collection.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "rating": 1
      },
      "output": {
        nearDocs: {
          $addToSet: "$_id",
          window: {
            documents: [
              -3,
              3
            ]
          }
        }
      }
    }
  },
  {
    "$sample": {
      "size": 1
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "localField": "nearDocs",
      "foreignField": "_id",
      "as": "nearDocs"
    }
  },
  {
    "$unwind": "$nearDocs"
  },
  {
    $match: {
      $expr: {
        $ne: [
          "$_id",
          "$nearDocs._id"
        ]
      }
    }
  },
  {
    "$sample": {
      "size": 1
    }
  },
  {
    "$project": {
      matchPairs: [
        {
          _id: "_id",
          name: "$name",
          rating: "$rating"
        },
        "$nearDocs"
      ]
    }
  },
  {
    "$unwind": "$matchPairs"
  },
  {
    "$replaceRoot": {
      "newRoot": "$matchPairs"
    }
  }
])

Here is the Mongo Playground for your reference.