Mongo, match array of arrays

Question

I have an object that has an array of sub objects on it:

    _id: "9",
        clients: [
            {
                id: 677,
                enabled: true,
                updated: 0,
                created: 1352416600
            },
            {
                id: 668,
                enabled: true,
                updated: 0,
                created: 1352416600
            }
        ],
        cloud: false,
        name: "love",
}

The user makes a request for images with client id 677, the above object will be returned

The user makes a request for images with client ids 677 and 668, the image above is returned

The user makes a request for images with client ids 677, 668, 690, the above image isn't returned

Im using PHP and Mongo DB. The MYSQL query that used to power this used to use a COUNT and sub query.

I have no idea where to even start tackling this in Mongo.

Any help appreciated.

MatheusOl · Accepted Answer · 2013-02-18 16:38:59Z

2

To search into documents inside arrays, you can use dot notation and "$and" operator.

Syntax:

db.coll.find({"$and": [{"clients.id": <id1>}, {"clients.id": <id2>}, ... ]});

For you samples:

1) The user makes a request for images with client id 677 (for only one item, there is no need of "$and", but you can use anyway):

db.coll.find({"clients.id": 677});

or

db.coll.find({"$and": [{"clients.id": 677}]});

2) The user makes a request for images with client ids 677 and 668:

db.coll.find({"$and": [{"clients.id": 677}, {"clients.id": 668}]});

3) The user makes a request for images with client ids 677, 668, 690:

db.coll.find({"$and": [{"clients.id": 677}, {"clients.id": 668}, {"clients.id": 690}]});

edited Feb 18, 2013 at 16:38

answered Feb 15, 2013 at 14:29

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

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

4 Comments

JohnnyHK Over a year ago

2) and 3) aren't correct as you can't have the same key multiple times in one JS object. Using $and like you show should be fine.

MatheusOl Over a year ago

@johnnyHK, depending on the language you can. And seems that the mongo shell accept it as well. Notice that I'm not storing it, just using in a query. But yes, the "$and" options seems pretty better.

JohnnyHK Over a year ago

@MatheusdeOliveira The mongo shell will accept it, but what it's doing is only using one of the fields (probably the last one).

MatheusOl Over a year ago

@JohnnyHK, you are right, I edited the answer to consider only the "$and". Sorry for the mistake.

JohnnyHK · Accepted Answer · 2013-02-15 14:52:47Z

1

For cases where you want to return docs where an array field contains each of a list of items you can use the $all operator.

So your three cases could be handled as:

1) The user makes a request for images with client id 677:

db.coll.find({'clients.id': 677});

2) The user makes a request for images with client ids 677 and 668:

db.coll.find({'clients.id': {$all: [677, 668]}});

3) The user makes a request for images with client ids 677, 668, and 690:

db.coll.find({'clients.id': {$all: [677, 668, 690]}});

answered Feb 15, 2013 at 14:52

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

Comments

Alex · Accepted Answer · 2013-02-15 14:44:23Z

0

There is a third and easier method.

db.foo.find({"clients.id":{$in:[677,688,690]}});

Hope this helps

/Nodex

Edit:

If you need to match the exact structure you can use $elemMatch for that

answered Feb 15, 2013 at 14:44

Alex

212 bronze badges

1 Comment

MatheusOl Over a year ago

The "$in" operator would match any, not all. The $all operator or the $and seems to be the right ways.

Collectives™ on Stack Overflow

Mongo, match array of arrays

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related