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
CodePudding user response:
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
})