Mongodb query to search values between range

Question

I am learning to write MongoDb queries. Need some help to pull the matching documents for the below cases.

BSON data/Documents:

[
  {
    "_id": "621f55b0f1b042003dd1e89a",
    "name": "L1",
    "service": [
      {
        "p1": "OBJ1",
        "p2": "955",
        "range": {
          "min": 955,
          "max": 955
        }
      }
    ]
  },
  {
    "_id": "621f55b0f1b042003dd1e89b",
    "name": "L2",
    "service": [
      {
        "p1": "OBJ1",
        "p2": "90",
        "range": {
          "min": 90,
          "max": 90
        }
      },
      {
        "p1": "OBJ2",
        "p2": "22-24",
        "range": {
          "min": 22,
          "max": 24
        }
      },
      {
        "p1": "OBJ1",
        "p2": "20-25",
        "range": {
          "min": 20,
          "max": 25
        }
      }
    ]
  },
  {
    "_id": "621f55b0f1b042003dd1e89c",
    "name": "L3",
    "service": [
      {
        "p1": "OBJ1",
        "p2": "23",
        "range": {
          "min": 23,
          "max": 23
        }
      }
    ]
  },
  {
    "_id": "621f55b0f1b042003dd1e89d",
    "name": "L4",
    "service": [
      {
        "p1": "OBJ2",
        "p2": "24",
        "range": {
          "min": 24,
          "max": 24
        }
      }
    ]
  },
  {
    "_id": "621f55b0f1b042003dd1e89e",
    "name": "L5",
    "service": [
      {
        "p1": "OBJ1",
        "p2": "22-26",
        "range": {
          "min": 22,
          "max": 26
        }
      }
    ]
  },
  {
    "_id": "621f55b0f1b042003dd1e89f",
    "name": "L6",
    "service": [
      {
        "p1": "OBJ1",
        "p2": "24-29",
        "range": {
          "min": 24,
          "max": 29
        }
      }
    ]
  },
  {
    "_id": "621f55b0f1b042003dd1e89g",
    "name": "L7",
    "service": [
      {
        "p1": "OBJ2",
        "p2": "21-25",
        "range": {
          "min": 21,
          "max": 25
        }
      }
    ]
  },
  {
    "_id": "621f55b0f1b042003dd1e89h",
    "name": "L2",
    "service": [
      {
        "p1": "OBJ1",
        "p2": "143",
        "range": {
          "min": 143,
          "max": 143
        }
      },
      {
        "p1": "OBJ2",
        "p2": "23",
        "range": {
          "min": 23,
          "max": 23
        }
      },
      {
        "p1": "OBJ1",
        "p2": "20-26",
        "range": {
          "min": 20,
          "max": 26
        }
      }
    ]
  }
]

I tried to construct the following queries but it doesn't work as expected.

Query 1:

db.collection.find({
  "service.p1": "OBJ1",
  "service": {
    "$elemMatch": {
      "range.max": {
        $gte: 27,
        $lte: 27
      },
      "range.min": {
        $gte: 27,
        $lte: 27
      }
    }
  }
})

Query 2:

db.collection.find({
  "service.p1": "OBJ1",
  "$expr": {
    "$and": [
      {
        "$lte": [
          "$service.range.max",
          23
        ]
      },
      {
        "$gte": [
          "$service.range.min",
          25
        ]
      }
    ]
  }
})

Following are the test cases that I want to execute.

Test case1:

Input: {p1: "OBJ1", values: {a: 23, b: 23}}
Output: Documents with the following Object Ids should be retrieved.
621f55b0f1b042003dd1e89b,
621f55b0f1b042003dd1e89c,
621f55b0f1b042003dd1e89e,
621f55b0f1b042003dd1e89h

Test case2:

Input: {p1: "OBJ2", values: {a: 23, b: 24}}
Output: Documents with the following Object Ids should be retrieved.
621f55b0f1b042003dd1e89b,
621f55b0f1b042003dd1e89d,
621f55b0f1b042003dd1e89g,
621f55b0f1b042003dd1e89h

Test case3:

Input: {p1: "OBJ1", values: {a: 25, b: 26}}
Output: Documents with the following Object Ids should be retrieved.
621f55b0f1b042003dd1e89b,
621f55b0f1b042003dd1e89e,
621f55b0f1b042003dd1e89f,
621f55b0f1b042003dd1e89h

Test case4:

Input: {p1: "OBJ2", values: {a: 21, b: 22}}
Output: Documents with the following Object Ids should be retrieved.
621f55b0f1b042003dd1e89b,
621f55b0f1b042003dd1e89g

Test case5:

Input: {p1: "OBJ1", values: {a: 26, b: 26}}
Output: Documents with the following Object Ids should be retrieved.
621f55b0f1b042003dd1e89e,
621f55b0f1b042003dd1e89f
621f55b0f1b042003dd1e89h

Mongo Playground: https://mongoplayground.net/p/CHJ7VnMyNwf

Fernando Jaimes · Accepted Answer · 2022-09-12 19:28:59Z

1

You can use the $and or $or at the top of the search. The query should look like this

db.collection.find({
  service: {
    $elemMatch: {
      "$and": [
        {
          "p1": "OBJ2",
          
        },
        {
          "range.max": {
            "$gte": 21
          }
        },
        {
          "range.min": {
            "$lte": 22
          }
        }
      ]
    }
  }
},
{
  _id: 1
})

edited Sep 12, 2022 at 19:28

answered Sep 12, 2022 at 16:53

Fernando Jaimes

765 bronze badges

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

2 Comments

Mahavir Munot Over a year ago

its returning incorrect records. You can check it here mongoplayground.net/p/CHJ7VnMyNwf

Fernando Jaimes Over a year ago

You're right was necessary add the '$elemMatch' Note: you can remove the '{_id:1}' at the end to see the full document

Collectives™ on Stack Overflow

Mongodb query to search values between range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related