CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
skodret solutions
skodret solutions

Reputation: 35

Use regex in expr in Mongodb aggragtion pipeline

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" }
    ])

Upvotes: 0

Views: 246

Answers (1)

ray
ray

Reputation: 15276

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.

Upvotes: 1

Related Questions