MongoDB/Mongoid: search for documents matching first item in array

Question

I have a document that has an array:

{
    _id: ObjectId("515e10784903724d72000003"),
    association_chain: [
        {
            name: "Product",
            id: ObjectId("4e1e2cdd9a86652647000003")
        }
    ],
    //...
}

I'm trying to search the collection for documents where the name of the first item in the association_chain array matches a given value.

How can I do this using Mongoid? Or if you only know how this can be done using MongoDB, if you post an example, then I could probably figure out how to do it with Mongoid.

Chris Heald · Accepted Answer · 2013-04-08 22:51:20Z

5

Use the positional operator. You can query the first element of an array with .0 (and the second with .1, etc).

> db.items.insert({association_chain: [{name: 'foo'}, {name: 'bar'}]})
> db.items.find({"association_chain.0.name": "foo"})
{ "_id" : ObjectId("516348865862b60b7b85d962"), "association_chain" : [ { "name" : "foo" }, { "name" : "bar" } ] }

You can see that the positional operator is in effect since searching for foo in the second element doesn't return a hit...

> db.items.find({"association_chain.1.name": "foo"})
>

...but searching for bar does.

> db.items.find({"association_chain.1.name": "bar"})
{ "_id" : ObjectId("516348865862b60b7b85d962"), "association_chain" : [ { "name" : "foo" }, { "name" : "bar" } ] }

You can even index this specific field without indexing all the names of all the association chain documents:

> db.items.ensureIndex({"association_chain.0.name": 1})
> db.items.find({"association_chain.0.name": "foo"}).explain()
{
        "cursor" : "BtreeCursor association_chain.0.name_1",
        "nscanned" : 1,
        ...

}
> db.items.find({"association_chain.1.name": "foo"}).explain()
{
        "cursor" : "BasicCursor",
        "nscanned" : 3,
        ...
}

edited Apr 8, 2013 at 22:51

answered Apr 8, 2013 at 22:46

Chris Heald

62.8k10 gold badges131 silver badges143 bronze badges

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

1 Comment

pestilence669 Over a year ago

I had no idea you could abuse indices like this. Love it.

Tilo · Accepted Answer · 2013-06-17 16:20:48Z

3

+500

Two ways to do this:

1) if you already know that you're only interested in the first product name appearing in "association_chain", then this is better:

db.items.find("association_chain.0.name":"something")

Please note that this does not return all items, which mention the desired product, but only those which mention it in the first position of the 'association_chain' array.

If you want to do this, then you'll need an index:

db.items.ensureIndex({"association_chain.0.name":1},{background:1})

2) if you are looking for a specific product, but you are not sure in which position of the association_chain it appears, then do this:

With the MongoDB shell you can access any hash key inside a nested structure with the '.' dot operator! Please note that this is independent of how deeply that key is nested in the record (isn't that cool?)

You can do a find on an embedded array of hashes like this:

db.items.find("association_chain.name":"something")

This returns all records in the collection which contain the desired product mentioned anywhere in the association_array.

If you want to do this, you should make sure that you have an index:

db.items.ensureIndex({"association_chain.name":1},{background: 1})

See "Dot Notation" on this page: http://docs.mongodb.org/manual/core/document/

edited Jun 17, 2013 at 16:20

answered Apr 8, 2013 at 22:59

Tilo

33.8k5 gold badges83 silver badges107 bronze badges

1 Comment

Matthew Schulkind Over a year ago

second code block should be 'db.items.ensureIndex', not '.find'

AntonioOtero · Accepted Answer · 2013-04-08 21:46:14Z

1

You can do this with the aggregation framework. In the mongo shell run a query that unwinds the documents so you have a document per array element (with duplicated data in the other fields), then group by id and any other field you want to include, plus the array with the operator $first. Then just include the $match operator to filter by name or mongoid.

Here's the query to match by the first product name:

db.foo.aggregate([
{ $unwind:"$association_chain"     
},
{
  $group : {
            "_id" : {
                "_id" : "$_id",
                "other" : "$other"
            },
            "association_chain" : {
                $first : "$association_chain"
            }
        }
},
{  $match:{ "association_chain.name":"Product"}
}

])

Here's how to query for the first product by mongoid:

db.foo.aggregate([
{ $unwind:"$association_chain"     
},
{
   $group : {
            "_id" : {
                "_id" : "$_id",
                "other" : "$other"
            },
            "association_chain" : {
                $first : "$association_chain"
            }
        }
},
{  $match:{ "association_chain.id":ObjectId("4e1e2cdd9a86652647000007")}
}

])

answered Apr 8, 2013 at 21:46

AntonioOtero

1,7891 gold badge14 silver badges16 bronze badges

1 Comment

AntonioOtero Over a year ago

The difference between using the Aggregation framework as I mentioned or using a simple query with Dot Notation is that using Dot Notation will return the whole document including all the elements of the array association_chain. Using this query with Aggregation will return the documents with only the element in the array that matched (the first), or you can transform the document any way you want. Use the one that suits best your needs.

Collectives™ on Stack Overflow

MongoDB/Mongoid: search for documents matching first item in array

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related