Home > Net >  Use regex in expr in Mongodb aggragtion pipeline
Use regex in expr in Mongodb aggragtion pipeline

Time:05-13

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.

  • Related