RegExp not working in prisma aggregateRaw for MongoDB

I’m new to mongo and prisma in javascript. This is a mongo query using prisma’s aggregateRaw method. The cond should do a case-insensitive string comparison. For example if $$property.property_name is “The Brough”, a regex /the br/i should be true.

const result = await prisma.owned_properties.aggregateRaw({
  pipeline: [
    { $match: { organization_id: { $oid: "6290eb7843e100027b70dd78" } } },
    {
      $project: {
        properties: {
          $filter: {
            input: "$properties",
            as: "property",
            cond: {
              // $eq: ["$$property.property_name", "The Brough"],
              $regexMatch: {
                input: "$$property.property_name",
                regex: new RegExp("The Brou", "i"),
                // regex: /The Brough/i,
              },
            },
          },
        },
        _id: 0,
      },
    },
  ],
})

Prisma throws an error it doesn’t like the RegExp or /regex/ notation. This same query works from mongosh command line though.

Error occurred during query execution: ConnectorError(ConnectorError { user_facing_error: None, kind: RawDatabaseError { code: "unknown", message: "Command failed (Location51105): Failed to optimize pipeline :: caused by :: $regexMatch needs 'regex' to be of type string or regex)" } })

Any ideas how to make it work? Basically just want the query to do a case-insensitive string comparison.

Answer

ORM’s like prisma have many layers and could cause certain issue’s like this, I personally did not find any reported issue but I did not look too deep.

Regardless I recommend the following workaround, just use $toLower to lowercase the property name before you execute the regex match, like so:

{
    $regexMatch: {
        input: {$toLower: "$$property.property_name"},
        regex: 'the brough',
    },
}

Mongo Playground