note: I'm using Mongodb 4 and I must use aggregation, because this is a step of a bigger aggregation
Problem
How to find in a collection documents that contains fields that ends with value from another field in same document ?
Let's start with this collection:
db.regextest.insert([
{"first":"Pizza", "second" : "Pizza"},
{"first":"Pizza", "second" : "not pizza"},
{"first":"Pizza", "second" : "not pizza"}
])
and an example query for exact match:
db.regextest.aggregate([
{
$match : { $expr: { $eq: [ "$first" ,"$second" ] } } }
])
I will get a single document
{
"_id" : ObjectId("5c49d44329ea754dc48b5ace"),
"first" : "Pizza", "second" : "Pizza"
}
And this is good.
But how to do the same, but with endsWith?
I've openend another question for start with here that uses indexOfBytes . But indexOf return only first match, and not last one
Edit: I've found an acceptable answer (with a lot of custom logic, my hope is Mongodb team will solve this), here the solution:
db.regextest.aggregate([
{
$addFields : {
"tmpContains" : { $indexOfBytes: [ "$first", { $ifNull : [ "$second" , 0] } ] }
}
},
{
$match: { "tmpContains" : { $gt : -1 } }
},
{
$addFields : {
"firstLen" : { $strLenBytes: "$first" }
}
},
{
$addFields : {
"secondLen" : { $strLenBytes: "$second" }
}
},
{
$addFields : {
"diffLen" : { $abs: { $subtract : [ "$firstLen", "$secondLen"] } }
}
},
{
$addFields : {
"res" : { $substr: [ "$first", "$diffLen", "$firstLen"] }
}
},
{
$match : { $expr : { $eq: [ "$res" , "$second" ] }}
}
])