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

Do you know the user with id=3 final score or just the user_id? — Sede
– Sede, Commented Jul 23, 2019 at 6:59
@styvane Any situation, doesn't matter. (I can separate query) But I have to focus on performance. — Hide
– Hide, Commented Jul 23, 2019 at 7:01

mickl · Accepted Answer · 2019-07-23 06:26:43Z

1

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

answered Jul 23, 2019 at 6:26

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hide Over a year ago

Exactly what I want. Thanks :)

Hide Over a year ago

To boost performance, Should I trigger index on final_score field only?

mickl Over a year ago

@Hide not sure if it can make any difference, you can try to measure the performance with and without an index

mickl Over a year ago

@Hide you can somehow improve the perf by indexing user_id

Timothy Lee · Accepted Answer · 2019-07-23 06:54:01Z

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

Collectives™ on Stack Overflow

MongoDB conditional query with self data

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related