Query using array of embedded documents

[Martinus Martinus]

Hi,

If I have an array of embedded documents to be put into query like below :

[{

    "name" : "Bruno",

    "type" : "dog"

 },

 {

    "name" : "Brownie",

    "type" : "cat"

 }]

and I have my collections looks like this :

{

   "_id" : 1,

   "zoo" : {

                "place" : "Brazil",

                "name" : "Bruno",

                "type" : "dog"

   }

},

{

   "_id" : 2,

   "zoo" : {

                "place" : "Japan",

                "name" : "Brownie",

                "type" : "cat"

   }

}

What kind of advanced query command I can use to get all the documents that at least match one of the element in the array ("name" and "type") without need to do loop?

Thanks.

[Jenna]

Hi Martinus,
Approximately how big is the array of embedded docs to be included in
the query? I was able to find matching documents using the $and and
$or operators, but the query I used could get unwieldy if the array is
large. Is this what you're trying to accomplish?

> db.collection.find({$or: [{$and: [{"zoo.name":"Bruno"},{"zoo.type":"dog"}]}, {$and:[{"zoo.name": "Brownie"}, {"zoo.type":"cat"}]}]})
{ "_id" : 1, "zoo" : { "place" : "Brazil", "name" : "Bruno", "type" :
"dog" } }
{ "_id" : 2, "zoo" : { "place" : "Japan", "name" : "Brownie", "type" :
"cat" } }

[Jenna]

I just realized the $and operator above is superfluous since
"zoo.name" and "zoo.type" are different fields.

>db.collection.find({$or: [{"zoo.name":"Bruno","zoo.type":"dog"}, {"zoo.name": "Brownie","zoo.type":"cat"}]})

{ "_id" : 1, "zoo" : { "place" : "Brazil", "name" : "Bruno", "type" :
"dog" } }
{ "_id" : 2, "zoo" : { "place" : "Japan", "name" : "Brownie", "type" :
"cat" } }

I can also think of a way to do this query with the new aggregation
framework, which is available in MongoDB version 2.2. I'll keep
thinking about the best way to solve this problem, but please let me
know if I missed the mark and you're attempting to do something
different.

New aggregation framework: http://docs.mongodb.org/manual/reference/aggregation/#_S_match

[Martinus Martinus]

Hi Jenna,

Thanks for your answer, actually I'm looking something like below :

a = ["Bruno", "Brownie", "Teddy"]

and if I have collection like :

{

   "_id" : 1,

   "zoo" : {

                "place" : "Brazil",

                "name" : "Bruno",

                "type" : "dog"

   }

},

{

   "_id" : 2,

   "zoo" : {

                "place" : "Japan",

                "name" : "Brownie",

                "type" : "cat"

   }

}

I can do search using db.collection.find({"zoo.name" : {$in : a}}). But how to do that if the array is an array of embedded documents?

Thanks.

--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

[Sam Millman]

If you structure like so:

{

   "_id" : 1,

   "zoo" : [{

                "place" : "Brazil",

                "name" : "Bruno",

                "type" : "dog"

   },
   {

                "place" : "Japan",

                "name" : "Brownie",

                "type" : "cat"

   }]

},


Your query should work both ways. Is that what you meant?

[Martinus Martinus]

Hi Sam,

The "zoo" only has one embedded document inside as below :

{

   "_id" : 1,

   "zoo" : {

                "place" : "Brazil",

                "name" : "Bruno",

                "type" : "dog"

   }

},

{

   "_id" : 2,

   "zoo" : {

                "place" : "Japan",

                "name" : "Brownie",

                "type" : "cat"

   }

}

And my query array has embedded documents as its elements : [{"name" : "Bruno", "type" : "dog"}, {"name" : "Brownie", "type" : "cat"}]. So I want to use this array to query from above collection without need to do for loop to each element inside it.

Thanks.

[Jenna]

Hi Martinus,

Can you reformat [{"name" : "Bruno", "type" : "dog"}, {"name" :
"Brownie", "type" : "cat"}]

to
newArray = [{"zoo.name" : "Bruno", "zoo.type" : "dog"}, {"zoo.name" :
"Brownie", "zoo.type" : "cat"}]

and use the query I posted above?
db.collection.find({$or: newArray})

Or is reformatting the array not an option?

> > On 25 July 2012 10:14, Martinus Martinus <martinus...@gmail.com> wrote:
>
> >> Hi Jenna,
>
> >> Thanks for your answer, actually I'm looking something like below :
>
> >> a = ["Bruno", "Brownie", "Teddy"]
>
> >> and if I have collection like :
>
> >> {
> >>    "_id" : 1,
> >>    "zoo" : {
> >>                 "place" : "Brazil",
> >>                 "name" : "Bruno",
> >>                 "type" : "dog"
> >>    }
> >> },
> >> {
> >>    "_id" : 2,
> >>    "zoo" : {
> >>                 "place" : "Japan",
> >>                 "name" : "Brownie",
> >>                 "type" : "cat"
> >>    }
> >> }
>
> >> I can do search using db.collection.find({"zoo.name" : {$in : a}}). But
> >> how to do that if the array is an array of embedded documents?
>
> >> Thanks.
>

[Jenna]

You can also use the new aggregation framework, which is available in
MongoDB version 2.2. The new aggregation framework will allow you to
keep the original array structure.
array = [{"name" : "Bruno", "type" : "dog"}, {"name" : "Brownie",
"type" : "cat"}]

> db.collection.aggregate({$project: {name: "$zoo.name", type:"$zoo.type"}},{$match:{$or: array}})

However, it's best to use the $match operator as early as possible in
the aggregate command to prevent a table scan. I would recommend
experimenting with the new aggregation framework if you think
upgrading to MongoDB version 2.2 will be an option.

aggregation framework: http://docs.mongodb.org/manual/applications/aggregation/