I have Country Table
{
"_id" : ObjectId("627cd43f48aea72fdc0d88e0"),
"county" : "india"
},
{
"_id" : ObjectId("627cd43f48aea72fdc0d88e1"),
"county" : "china"
}
And City Table
{
"_id" : ObjectId("627cd43f48aea72fdc0d88e0"),
"county_name" : "India"
},
{
"_id" : ObjectId("627cd43f48aea72fdc0d88e1"),
"county_name" : "China"
}
In Country Table Country name is in lower letter and In City Table Country Name is in Upper Letter. So Now how can use like condition to join both collection with matching same name. Ex. Get Data of india Like India
db.getCollection('countryTable').aggregate([
{
$lookup: {
from: "cityTable",
let: { county: "$county" },
pipeline: [{
$match: {
"$expr": {
"$regexMatch": {
"input": "$county_name",
"regex": "$$county",
"options": "i"
}
}
}
}],
as: "citydetails"
}
},
{ $unwind: "$citydetails" }
])
CodePudding user response:
With MongoDB v4.0, you can do the $match
in subpipeline with $toLower
db.countryTable.aggregate([
{
$lookup: {
from: "cityTable",
let: {
county: "$county"
},
pipeline: [
{
$match: {
"$expr": {
$eq: [
{
"$toLower": "$$county"
},
{
"$toLower": "$county_name"
}
]
}
}
}
],
as: "citydetails"
}
},
{
$unwind: "$citydetails"
}
])
Here is the Mongo playground for your reference.