How to retrieve element only if the whole list of string matches a regex in MongoDB-CodePudding

Here’s the context, I have elements in database:

db.collection.insertMany([
{ ‘_id’: 1, ‘items’: [‘abc’, ‘ab’]},
{ ‘_id’: 2, ‘items’: [‘abc’, ‘bc’]},
])

I want to retrieve elements with ALL items matching my regex, in this case, I want it to match if first letter is an ‘a’.

I tried:

db.collection.find({
“items”:{ $regex : /^a/}}
})

But it seems that it matches the second element in our example also, because one of the items matches the regex, and I need both to match.

I tried other operator such as $all and $and but I couldn’t make it.

Thanks in advance for your help

CodePudding user response：

You can achieve it using aggregation framework

playground

db.collection.aggregate([
  {
    "$project": {
      "items": "$items",
      // preserve original array
      "a": {
        "$filter": {
          //To check each array item
          "input": "$items",
          "as": "item",
          "cond": {
            "$regexFind": {
              "input": "$$item",
              "regex": "a",
              "options": "i"
            }
          }
        }
      }
    }
  },
  {
    "$match": {
      "$and": [//Check for matching condition
        {
          $expr: {
            "$ne": [//skip unmatched docs
              "$a",
              null
            ]
          }
        },
        {
          $expr: {//check for all the elements match
            "$eq": [
              {
                "$size": "$a"
              },
              {
                "$size": "$items"
              }
            ]
          }
        }
      ]
    }
  }
])