How to retrieve element only if the whole list of string matches a regex

[Alexandre_Baron]

Hello,

Apologies because I didn’t know what category this topics belongs to.

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 of the item 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

[Jason_Tran]

Hi @Alexandre_Baron - Welcome to the community!

Thanks for providing the sample documents and what you’ve tried so far

I tried:
db.collection.find({
“items”:{ $regex : /^A/}}
})
But it seems that it matches the second element in our example also, because of the item matches the regex, and I need both to match.

This is slightly surprising as the $regex value is an uppercase A whilst the sample documents contain items elements beginning with lowercase a which I would expect none of the documents to be returned. However, this could be a typo. In the case that this was a typo (perhaps a lowercase a in the query you’ve provided) when writing the post, the reason for both documents being returned is described in the Query an Array for an element documentation:

To query if the array field contains at least one element with the specified value, use the filter { <field>: <value> } where <value> is the element value.

In saying so, based off the description, I would assume your expected / desired output / returned document(s) would be:

{ ‘_id’: 1, ‘items’: [‘abc’, ‘ab’]}

I.e, the only document that contains all elements of the items array that begin with an a. Correct me if I am wrong here.

One method that may possibly work for you is to use an aggregation operation with the following stages shown in the example below:

/// Sample documents in the database
arraydb> db.collection.find()
[
  { _id: 1, items: [ 'abc', 'ab' ] },
  { _id: 2, items: [ 'abc', 'bc' ] }
]

/// Aggregation operation to get all documents where all elements in the `items` array match the regex
db.collection.aggregate([
    {$match: {items: /^a/}}, 
    {$addFields: { 
        xx: {$subtract: [
            {$size: '$items'},
            {$size: {$filter: {
                input: '$items',
                cond: {$regexMatch: {input: '$$this', regex:/^a/}}
            }}}
        ]}
    }},
    {$match: {xx: 0}},
    {$unset: 'xx'}
])

/// Output
[ { _id: 1, items: [ 'abc', 'ab' ] } ]

For your reference regarding the above pipeline, here is a brief description of what is occuring at each stage:

1. First $match stage - Retrieve all documents with the matching regex /^a/
2. The $addFields stage - Add calculated xx field that displays the subtraction of the length of the items array with the length of a filtered items array which filters out non-matching array elements against the regex pattern. Any xx fields with a value of 0 would represent a document in which all elements of the items array did begin with a
3. Second $match stage on {xx: 0} - Return all documents that have xx values of 0, meaning that the original array and the filtered array are the same length, which in turn means that all elements in the array matches the regex
4. The $unset stage - Remove the xx field from final output

For the above example aggregation, the first $match should be able to make use of indexes.

Please note that this was just performed in my test environment with the sample documents provided. Please ensure you test this to see if it suits your use case and environment.

If further assistance is required, please provide the following details:

1. More context regarding the use case
2. MongoDB version

Hope this helps.

Regards,
Jason

[Alexandre_Baron]

Hi,
Thanks a lot for the solution, and more importantly the explanation.
Your assumptions were right regarding my request

[Jason_Tran]

Glad to hear it and thanks for confirming the solution!