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.
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',
},
}