Regex inside array in mongoDB

Question

i want to do a query inside a array in mongodb with regex, the collections have documents like this:

{
"_id" : ObjectId("53340d07d6429d27e1284c77"),
"company" : "New Company",
"worktypes" : [ 
    {
        "name" : "Pompas",
        "works" : [ 
            {
                "name" : "name 2",
                "code" : "A00011",
                "price" : "22,22"
            }, 
            {
                "name" : "name 3",
                "code" : "A00011",
                "price" : "22,22"
            }, 
            {
                "name" : "name 4",
                "code" : "A00011",
                "price" : "22,22"
            }, 
            {
                "code" : "asdasd",
                "name" : "asdads",
                "price" : "22"
            }, 
            {
                "code" : "yy",
                "name" : "yy",
                "price" : "11"
            }
        ]
    }, 
    {
        "name" : "name 4",
        "works" : [ 
            {
                "code" : "A112",
                "name" : "Nombre",
                "price" : "11,2"
            }
        ]
    },          
    {
        "name" : "ee",
        works":[

            {
                "code" : "aa",
                "name" : "aa",
                "price" : "11"
            }, 
            {
                "code" : "A00112",
                "name" : "Nombre",
                "price" : "12,22"
            }
              ]
    }
]

}

Then i need to find a document by the company name and any work inside it have match a regex in code or name work. I have this:

var companyquery = { "company": "New Company"};
var regQuery = new RegExp('^A0011.*$', 'i');

db.categories.find({$and: [companyquery,
            {$or: [
                {"worktypes.works.$.name": regQuery},
                {"worktypes.works.$.code": regQuery}
            ]}]})

But dont return any result..I think the error is try to search inside array with de dot and $.. Any idea?

Edit:

With this:

db.categories.find({$and: [{"company":"New Company"},
            {$or: [
                {"worktypes.works.name": {"$regex": "^A00011$|^a00011$"}},
                {"worktypes.works.code": {"$regex": "^A00011$|^a00011$"}}
            ]}]})

This is the result:

{
    "_id" : ObjectId("53340d07d6429d27e1284c77"),
    "company" : "New Company",
    "worktypes" : [ 
        {
            "name" : "Pompas",
            "works" : [ 
                {
                    "name" : "name 2",
                    "code" : "A00011",
                    "price" : "22,22"
                }, 
                {
                    "code" : "aa",
                    "name" : "aa",
                    "price" : "11"
                }, 
                {
                    "code" : "A00112",
                    "name" : "Nombre",
                    "price" : "12,22"
                }, 
                {
                    "code" : "asdasd",
                    "name" : "asdads",
                    "price" : "22"
                }, 
                {
                    "code" : "yy",
                    "name" : "yy",
                    "price" : "11"
                }
            ]
        }, 
        {
            "name" : "name 4",
            "works" : [ 
                {
                    "code" : "A112",
                    "name" : "Nombre",
                    "price" : "11,2"
                }
            ]
        }, 
        {
            "name" : "Bombillos"
        }, 
        {
            "name" : "Pompas"
        }, 
        {
            "name" : "Bombillos 2"
        }, 
        {
            "name" : "Other type"
        }, 
        {
            "name" : "Other new type"
        }
    ]
}

The regex dont field the results ok..

Its a string with value for regex, i have copy my code from nodejs client, i will edit my questio. Thanls — colymore
– colymore, Commented Apr 7, 2014 at 10:01
I think one of the problems is that the positional operator $ is a projection/update operator, not a query operator, try something closer to: "worktypes.works.name" rather than "worktypes.works.$.name" — Sammaye
– Sammaye, Commented Apr 7, 2014 at 10:15
Wait what result do you want? That document does fill the results — Sammaye
– Sammaye, Commented Apr 7, 2014 at 11:02

Neil Lunn · Accepted Answer · 2014-04-07 12:34:26Z

13

You are using a JavaScript native RegExp object for the regular expression, however for mongo to process the regular expression it needs to be sent as part of the query document, and this is not the same thing.

Also the regex will not match the values that you want. It could actualy be ^A0111$ for the exact match, but your case insensitive match causes a problem causing a larger scan of a possible index. So there is a better way to write that. Also see the documentation link for the problems with case insensitive matches.

Use the $regex operator instead:

db.categories.find({
    "$and": [
        {"company":"New Company"},
        { "$or": [
            { "worktypes.works.name": { "$regex": "^A00011$|^a00011$" }},
            { "worktypes.works.code": { "$regex": "^A00011$|^a00011$" }}
        ]}
    ]
})

Also the positional $ placeholders are not valid for a query, they are only used in projection or an update or the first matching element found by the query.

But your actual problem seems to be that you are trying to only get the elements of an array that "match" your conditions. You cannot do this with .find() and for that you need to use .aggregate() instead:

db.categories.aggregate([

    // Always makes sense to match the actual documents
    { "$match": {
        "$and": [
            {"company":"New Company"},
            { "$or": [
                { "worktypes.works.name": { "$regex": "^A00011$|^a00011$" }},
                { "worktypes.works.code": { "$regex": "^A00011$|^a00011$" }}
            ]}
        ]
    }},

    // Unwind the worktypes array
    { "$unwind": "$worktypes" },

    // Unwind the works array
    { "$unwind": "$worktypes.works" },

    // Then use match to filter only the matching entries
    { "$match": {
       "$or": [
            { "worktypes.works.name": { "$regex": "^A00011$|^a00011$" } },
            { "worktypes.works.code": { "$regex": "^A00011$|^a00011$" } }
        ]
    }},

    /* Stop */
    // If you "really" need the arrays back then include all the following
    // Otherwise the steps up to here actually got you your results

    // First put the "works" array back together
    { "$group": {
        "_id": {
            "_id": "$_id",
            "company": "$company",
            "workname": "$worktypes.name"
        },
        "works": { "$push": "$worktypes.works" }
    }},

    // Then put the "worktypes" array back
    { "$group": {
        "_id": "$_id._id",
        "company": { "$first": "$_id.company" },
        "worktypes": {
            "$push": {
                "name": "$_id.workname",
                "works": "$works"
            } 
        } 
    }}
])

So what .aggregate() does with all of these stages is it breaks the array elements into normal document form so they can be filtered using the $match operator. In that way, only the elements that "match" are returned.

What "find" is correctly doing is matching the "document" that meets the conditions. Since documents contain the elements that match then they are returned. The two principles are very different things.

When you mean to "filter" use aggregate.

edited Apr 7, 2014 at 12:34

answered Apr 7, 2014 at 10:23

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

9 Comments

Neil Lunn Over a year ago

@colymore Well the document in the result does actually contain elements that match the expression. So are you actually trying to "filter" the results of the array to only return the matches? That is a different thing.

colymore Over a year ago

Im trying to get only the worktypes.works that his worktype.work.code or name match the regex expression, and discard the worktypes.works that not..

Sammaye Over a year ago

The RegExp object will be translated to the BSON regex object just as it shows in the documentation. The BSON regex object does not directly translate to the $regex operator.

Neil Lunn Over a year ago

@colymore Which is what I said in my comment. You are actually asking for something that "find" does not do. See the additional information.

Neil Lunn Over a year ago

@colymore So I actually have spent the time and replicated your data and ran though the final aggregate expression given. Edits are in the response, and the full chain works as expected. So there is no reason for you to not accept this. I have pointed out several problems with your original approach and given a correct solution to actually filter the array. It would be decent to actually accept the advise you are given :)

|

aelor · Accepted Answer · 2014-04-07 10:09:20Z

2

i think there is a typo :

the regex should be : ^A00011.*$

triple 0 instead of double 0

answered Apr 7, 2014 at 10:09

aelor

11.2k3 gold badges35 silver badges48 bronze badges

1 Comment

colymore Over a year ago

Not, same result. I have tried with most of codes and names but any isnt work.

turivishal · Accepted Answer · 2021-10-25 12:55:00Z

You can try aggregate method and aggregation array operators, so this query will be supported from MongoDB 4.2,

$match to match your condition
$addFields to add/edit field in document
$map to iterate loop of worktypes array
$filter to iterate loop of works array and it will return the filtered result as per provided condition
$regexMatch to match regex expression same as we did in $match stage, it will return a boolean response, so we checked $or condition here,
$mergeObjects to merge current object of worktypes and updated works array property
second $addFields for remove empty result of works array
$filter to iterate loop of worktypes array and check negative condition to remove empty works document

db.categories.aggregate([
  {
    $match: {
      $and: [
        { "company": "New Company" },
        {
          $or: [
            { "worktypes.works.name": { "$regex": "^A00011$|^a00011$" } },
            { "worktypes.works.code": { "$regex": "^A00011$|^a00011$" } }
          ]
        }
      ]
    }
  },
  {
    $addFields: {
      worktypes: {
        $map: {
          input: "$worktypes",
          in: {
            $mergeObjects: [
              "$$this",
              {
                works: {
                  $filter: {
                    input: "$$this.works",
                    cond: {
                      $or: [
                        {
                          $regexMatch: {
                            input: "$$this.name",
                            regex: "^A00011$|^a00011$"
                          }
                        },
                        {
                          $regexMatch: {
                            input: "$$this.code",
                            regex: "^A00011$|^a00011$"
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      worktypes: {
        $filter: {
          input: "$worktypes",
          cond: { $ne: ["$$this.works", []] }
        }
      }
    }
  }
])

Playground

Collectives™ on Stack Overflow

Regex inside array in mongoDB

3 Answers 3

9 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related