MongoDB conditional query with self data

Question

Assume that there is 4 users in collections.

> db.users.find().pretty()
{
    "_id" : ObjectId("5d369b451b48d91cba76c618"),
    "user_id" : 1,
    "final_score" : 65,
    "max_score" : 15,
    "min_score" : 15,
}
{
    "_id" : ObjectId("5d369b451b48d91cba76c619"),
    "user_id" : 2,
    "final_score" : 70,
    "max_score" : 15,
    "min_score" : 15,
}
{
    "_id" : ObjectId("5d369b451b48d91cba76c61a"),
    "user_id" : 3,
    "final_score" : 60,
    "max_score" : 15,
    "min_score" : 15,
}
{
    "_id" : ObjectId("5d369b451b48d91cba76c61b"),
    "user_id" : 4,
    "final_score" : 83,
    "max_score" : 15,
    "min_score" : 15,
}

I want to extract users that meet below conditions.

• final_score >= user_id=3's final_score + each document's max_score
• final_score <= user_id=3's final_score - each document's min_score

To represent with MySQL, it is very simple.

SELECT * FROM users
WHERE final_score <= 60 + users.max_score AND final_score >= 60 - users.min_score

But I wonder that how can I querying with mongodb?

Thanks.

EDIT

I think it can be execute with this.

So I made query like this.

db.users.find({
    'final_score': {
        '$lte': '60 + this.max_score',
        '$gte': '60 - this.min_score'
    }
})

But it return nothing

Answer 1

From your description, I guess you already know the score of user3 is 60.

In this case:

db.collection.aggregate([
  {
    $addFields: {
      match: {
        $and: [
          {
            $gte: [
              "$final_score",
              {
                $subtract: [
                  60,
                  "$min_score"
                ]
              }
            ]
          },
          {
            $lte: [
              "$final_score",
              {
                $add: [
                  60,
                  "$max_score"
                ]
              }
            ]
          }
        ]
      }
    }
  },
  {
    $match: {
      match: true
    }
  },
  {
    $project: {
      match: 0
    }
  }
])

mongoplayground

Answer 2

The difficulty here comes from the fact that you need to run two separate pipelines (one to get the value for user 3 and second one to filter all documents). In Aggregation Framework you can do that using $facet operator which allows you to run multiple pipelines and then keep processing data in subsequent steps. To compare the data you can use $filter and to get original shape as a result you need to transform nested array into separate documents using $unwind and $replaceRoot

db.users.aggregate([
    {
        $facet: {
            user3: [
                { $match: { user_id: 3 } }
            ],
            docs: [
                { $match: {} }
            ]
        }
    },
    {
        $addFields: {
            user3: { $arrayElemAt: [ "$user3", 0 ] }
        }
    },
    {
        $project: {
            docs: {
                $filter: {
                    input: "$docs",
                    cond: {
                        $and: [
                            { $lte: [ "$$this.final_score", { $add: [ "$user3.final_score", "$$this.max_score" ] } ] },
                            { $gte: [ "$$this.final_score", { $subtract: [ "$user3.final_score", "$$this.max_score" ] } ] },
                        ]
                    }
                }
            }
        }
    },
    {
        $unwind: "$docs"
    },
    {
        $replaceRoot: {
            newRoot: "$docs"
        }
    }
])

Mongo Playground