combining regex and embedded objects in mongodb queries

Question

I am trying to combine regex and embedded object queries and failing miserably. I am either hitting a limitation of mongodb or just getting something slightly wrong maybe someone out ther has encountered this. The documentation certainly does'nt cover this case.

data being queried:

{
    "_id" : ObjectId("4f94fe633004c1ef4d892314"),
    "productname" : "lightbulb",
    "availability" : [
            {
                    "country" : "USA",
                    "storeCode" : "abc-1234"
            },
            {
                    "country" : "USA",
                    "storeCode" : "xzy-6784"
            },
            {
                    "country" : "USA",
                    "storeCode" : "abc-3454"
            },
            {
                    "country" : "CANADA",
                    "storeCode" : "abc-6845"
            }
    ]
}

assume the collection contains only one record

This query returns 1:

db.testCol.find({"availability":{"country" : "USA","storeCode":"xzy-6784"}}).count();

This query returns 1:

db.testCol.find({"availability.storeCode":/.*/}).count();

But, this query returns 0:

db.testCol.find({"availability":{"country" : "USA","storeCode":/.*/}}).count();

Does anyone understand why? Is this a bug?

thanks

Adam Comerford · Accepted Answer · 2012-05-25 14:16:42Z

3

You are referencing the embedded storecode incorrectly - you are referencing it as an embedded object when in fact what you have is an array of objects. Compare these results:

db.testCol.find({"availability.0.storeCode":/x/});
db.testCol.find({"availability.0.storeCode":/a/});

Using your sample doc above, the first one will not return, because the first storeCode does not have an x in it ("abc-1234"), the second will return the document. That's fine for the case where you are looking at a single element of the array and pass in the position. In order to search all of the objcts in the array, you want $elemMatch

As an example, I added this second example doc:

{
    "_id" : ObjectId("4f94fe633004c1ef4d892315"),
    "productname" : "hammer",
    "availability" : [
            {
                    "country" : "USA",
                    "storeCode" : "abc-1234"
            },

    ]
}

Now, have a look at the results of these queries:

PRIMARY> db.testCol.find({"availability" : {$elemMatch : {"storeCode":/a/}}}).count();
2
PRIMARY> db.testCol.find({"availability" : {$elemMatch : {"storeCode":/x/}}}).count();
1

answered May 25, 2012 at 14:16

Adam Comerford

21.7k4 gold badges68 silver badges87 bronze badges

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

1 Comment

Florian Hehlen Over a year ago

thank you. That did the trick and I understand mongodb a bit better now.

Collectives™ on Stack Overflow

combining regex and embedded objects in mongodb queries

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