MongoDB aggregation - $regexMatch on array field

Question

I'm using Mongo's $regexMatch operator to find documents where at least part of a field matches the pattern, and this works fine for root-level fields. But how do I use it with array fields? I want to return a match if at least one of the array elements matches the pattern.

For example, let's say the collection has these two documents:

{
  "_id": ObjectId("5ff6335c1570ba63ca5ac21e"),
  "requirements": [
    {
      "description": "Bachelor of Science required for this blah blah blah",
      "code": "ABC"
    },
    {
      "description": "Also much experience in JavaScript blah",
      "code": "XYZ"
    }
  ]
},

{
  "_id": ObjectId("5ff6335b1570ba63ca5abefb"),
  "requirements": [
    {
      "description": "Master of Arts WANTED NOW!",
      "code": "TTT"
    },
    {
      "description": "5+ experience required in C++",
      "code": "QQQ"
    }
  ]
}

and something like this pipeline

db.Collection.aggregate([
  { $match:
     { $expr:
        { $regexMatch: { 
          input: '$requirements.description', 
          regex: /^.*?\bblah blah blah\b.*?$/im 
        } } 
     } 
  }
])

should return just the first document since its first element inside requirements matches on description containing "blah blah blah" ("Bachelor of Science required for this blah blah blah").

However this just throws me an error saying '$regexMatch needs input to be of type string'. And replacing it with $requirements[0].description doesn't work either.

So is there a way to regex match array fields in Mongo?

what is your expectation? it should return a single matching document? it should return score? can you show expected result, — turivishal
– turivishal, Commented Jan 12, 2021 at 6:37
I updated the example for clarity -- but the expectation is that it returns any documents that match the pattern (in this case, having "blah blah blah" as part of the description for at least one of their requirements) — yev
– yev, Commented Jan 12, 2021 at 14:46
oh wow that's so much simpler -- is there a way to do the same thing with $addFields instead of $match to add a score field too? — yev
– yev, Commented Jan 12, 2021 at 19:03

turivishal · Accepted Answer · 2021-01-12 19:20:33Z

3

$regexMatch allows only string input requirements having array It requires to iterate loop array values,

$reduce to iterate loop of description, check condition if expression match then return score otherwise return initial value

db.collection.aggregate([
  {
    $addFields: {
      score: {
        $reduce: {
          input: "$requirements.description",
          initialValue: 0,
          in: {
            $cond: [
              {
                $eq: [
                  {
                    $regexMatch: {
                      input: "$$this",
                      regex: "blah blah blah"
                    }
                  },
                  true
                ]
              },
              50,
              "$$value"
            ]
          }
        }
      }
    }
  }
])

Playground

If you want filter documents the simply try $regex in $match stage,

db.collection.aggregate([
  {
    $match: {
      "requirements.description": {
        $regex: "blah blah blah"
      }
    }
  }
])

Playground

edited Jan 12, 2021 at 19:20

answered Jan 12, 2021 at 19:15

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joachim Bülow Over a year ago

Tried too hard to make regexMatch work for an array of objects, when regex was right there :') Thanks

Collectives™ on Stack Overflow

MongoDB aggregation - $regexMatch on array field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related