MongoDb query match expression not working.
I have a posts collection and want to return only those posts which match the user Id of the user who created it but my query does not seem to be working.
Sample Dataset
[
// 1
{
"_id": ObjectId("6257047cffd61ab62864c1ae"),
"type": "A",
"source": "B",
"status": "A",
"user": ObjectId("622b55ff0b0af6b049c387d3")
},
// 2
{
"_id": ObjectId("6257047cffd61ab62864c1ad"),
"type": "B",
"source": "A",
"status": "A",
"user": ObjectId("622b55ff0b0af6b049c387d3")
},
// 3
{
"_id": ObjectId("6257047cffd61ab62864c1ce"),
"type": "A",
"source": "C",
"status": "B",
"user": ObjectId("622b55ff0b0af6b049c387d3")
},
// 4
{"_id": ObjectId("6257047cffd61ab62864c1cb"),
"type": "A",
"source": "B",
"status": "C",
"user": ObjectId("622b56250b0af6b049c387d6")
}
]
MongoDb Query:-
db.collection.aggregate([
{
$addFields: {
paramType: "All",
paramSource: "All",
paramStatus: "All",
},
},
{
$match: {
$expr: {
$and: [
{
user: ObjectId("622b55ff0b0af6b049c387d3")
},
{
$or: [
{
$eq: [
"$paramType",
"All"
],
},
{
$eq: [
"$paramType",
"$type"
],
},
],
},
{
$or: [
{
$eq: [
"$paramSource",
"All"
],
},
{
$eq: [
"$paramSource",
"$source"
],
},
],
},
{
$or: [
{
$eq: [
"$paramStatus",
"All"
],
},
{
$eq: [
"$paramStatus",
"$status"
],
},
],
},
],
},
},
},
{
$setWindowFields: {
output: {
totalCount: {
$count: {}
}
}
}
},
{
$sort: {
createdAt: -1
}
},
{
$skip: 0
},
{
$limit: 6
},
{
"$project": {
"paramSource": false,
"paramStatus": false,
"paramType": false,
}
}
])
Query Output:-
[
{
"_id": ObjectId("6257047cffd61ab62864c1ae"),
"source": "B",
"status": "A",
"totalCount": 4,
"type": "A",
"user": ObjectId("622b55ff0b0af6b049c387d3")
},
{
"_id": ObjectId("6257047cffd61ab62864c1ad"),
"source": "A",
"status": "A",
"totalCount": 4,
"type": "B",
"user": ObjectId("622b55ff0b0af6b049c387d3")
},
{
"_id": ObjectId("6257047cffd61ab62864c1ce"),
"source": "C",
"status": "B",
"totalCount": 4,
"type": "A",
"user": ObjectId("622b55ff0b0af6b049c387d3")
},
{
"_id": ObjectId("6257047cffd61ab62864c1cb"),
"source": "B",
"status": "C",
"totalCount": 4,
"type": "A",
"user": ObjectId("622b56250b0af6b049c387d6")
}
]
The query does not work output contains posts created by all users it is not filtering.
Advertisement
Answer
The $match part should look like this:
{
$match: {
$and: [
{
user: ObjectId("622b55ff0b0af6b049c387d3")
},
{
$or: [{paramType: {$eq: "All"}},
{$expr: {$eq: ["$paramType", "$type"]}}
]
},
{
$or: [{paramSource: {$eq: "All"}},
{$expr: {$eq: ["$paramSource", "$type"]}}
]
},
{
$or: [{paramStatus: {$eq: "All"}},
{$expr: {$eq: ["$paramStatus", "$type"]}}
]
}
]
}
}
The $expr should only be assigned to cases where both values are in the document. This query returns 3 / 4 documents, the ones in which user: ObjectId("622b55ff0b0af6b049c387d3")
BTW, the last 3 conditions on this $match stage are redundant, as they will always be true, since the query sets them with the value ‘All’ on the former stage