I want to filter the documents based on importkeyid and MissingPersonIds.PhotoId array field. The filter on MissingPersonIds.PhotoId should work like 'sql - like' operator.
Schema:
[{
"_id": {
"$oid": "61ada7da9a30fd8471869bbc"
},
"ImportKeyId": 5843,
"Name" : "AV"
"MissingPersonIds": [
{
"PhotoId": "2 - Copy.jpg",
"Description": "Account ID not found"
},
{
"PhotoId": "2 - Copy - Copy.jpg",
"Description": "Account ID not found"
},
{
"PhotoId": "2 - Copy - Copy (2).jpg",
"Description": "Account ID not found"
},
{
"PhotoId": "202020 - Copy (2).jpg",
"Description": "Account ID not found"
},
{
"PhotoId": "202020 - Copy - Copy.jpg",
"Description": "Account ID not found"
},
{
"PhotoId": "202020 - Copy - Copy (2).jpg",
"Description": "Account ID not found"
}
]
},
{
"_id": {
"$oid": "619cd7d2181999c9a4da790a"
},
"ImportKeyId": 5753,
"Name" : 'av1'
"MissingPersonIds": [
{
"PhotoId": "94578.jpg",
"Description": "Photo id is not found"
},
{
"PhotoId": "371426759.jpg",
"Description": "Photo id is not found"
}
]
}
.
.
.
]
Based on following conditions:
Filter: { "ImportKeyId" : 5843 }
Projection: { MissingPersonIds : { $slice:[0,5] }, "MissingPersonIds": { $elemMatch: { PhotoId : { $regex: /202020 /i } } } }
I was expecting below output but $elemMatch returns only one matched record
[{
"_id": {
"$oid": "61ada7da9a30fd8471869bbc"
},
"ImportKeyId": 5843,
"Name" : "AV"
"MissingPersonIds": [
{
"PhotoId": "202020 - Copy (2).jpg",
"Description": "Account ID not found"
},
{
"PhotoId": "202020 - Copy - Copy.jpg",
"Description": "Account ID not found"
}
]
}]
What should I used instead of $elemMatch
?
CodePudding user response:
Maybe something like this
db.collection.aggregate([
{
"$match": {
"ImportKeyId": 5843
}
},
{
"$project": {
"_id": 1,
"ImportKeyId": 1,
MissingPersonIds: {
$filter: {
input: "$MissingPersonIds",
as: "item",
cond: {
$gt: [
{
$indexOfCP: [
{
$toLower: "$$item.PhotoId"
},
"202020 "
]
},
-1
]
}
}
}
}
}
])
Output:
[
{
"_id": ObjectId("61ada7da9a30fd8471869bbc")
"ImportKeyId": 5843,
"MissingPersonIds": [
{
"Description": "Account ID not found",
"PhotoId": "202020 - Copy (2).jpg"
},
{
"Description": "Account ID not found",
"PhotoId": "202020 - Copy - Copy.jpg"
},
{
"Description": "Account ID not found",
"PhotoId": "202020 - Copy - Copy (2).jpg"
}
],
}
]
explained:
- In the match stage you filter only documents with the ImportKeyId: 5843
- With the project stage you fiter _id & ImportKeyId , and you add new field MissingPersonIds that will filter the elements in the array containing only strings like in SQL having " 2020 %" inside