But here in this case I want to know how MongoDB will create index on _id; is it on both _id.a and _id.b field or just on _id.b field?
The index will index the embedded document as a whole; i.e. the entirety of { "a" : 8, "b" : ObjectId(...) }. This is a different index entirely than one that would be built solely on _id.a, _id.b, or a compound index on both fields.
Note that querying on a nested document requires an exact match of the specified document. To illustrate:
> db.embed.insert({ "_id" : { "a" : 1, "b" : 2 } } )
WriteResult({ "nInserted" : 1 })
> db.embed.find( { "_id" : { "b" : 2, "a" : 1 } } )
>
> db.embed.find( { "_id" : { "a" : 1, "b" : 2 } } )
{ "_id" : { "a" : 1, "b" : 2 } }
If a single query will be filtering on _id.a, field1 and field2 (e.g. db.foo.find( { "_id.a" : 8, "field1" : "value1", "field2" : "value2" }) ) then then I would recommend building a compound index on the three fields, i.e. { "_id.a" : 1, "field1" : 1, "field2" : 1 }.
Update with more info for comment:
The two queries are not functionally equivalent; the 2nd query will only filter on { "_id": {$lt:{"b":5} }, "field1": "value1" }. For example, try the following:
db.foo.insert({ "_id" : { "a" : 1, "b" : 1 } } )
db.foo.insert({ "_id" : { "a" : 2, "b" : 2 } } )
db.foo.insert({ "_id" : { "a" : 2, "b" : 1 } } )
db.foo.insert({ "_id" : { "a" : 1, "b" : 2 } } )
db.foo.find({
"_id.a":{ "$gt" : 1 },
"_id.b":{ "$lt": 2}
})
db.foo.find({
"_id": { "$gt" : { "a": 1 } },
"_id": { "$lt" : { "b": 2 } }
})
Note that queries that filter on multiple fields have an implicit logical AND as long as the fields being filtered on are different. The second query will effectively only be filtering on "_id": { "$lt" : { "b": 2 } } as there are two filters on _id. The $and filter would be needed for the logical AND to be used as intended in this query.
That being said, these two queries are asking different things. The first query is filtering on the embedded values "_id.a" and "_id.b":
> db.foo.find({
... "_id.a" : { "$lt" : 2 }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
>
> db.foo.find({
... "_id.b" : { "$lt" : 2 }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 2, "b" : 1 } }
Whereas the second query is asking to return documents where _the entire value of id is greater than / less than the value specified. This can return unexpected results:
> db.foo.find({
... "_id": { "$lt" : { "a": 2 } }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
>
> db.foo.find({
... "_id": { "$lt" : { "b": 2 } }
... })
{ "_id" : { "a" : 1, "b" : 1 } }
{ "_id" : { "a" : 1, "b" : 2 } }
{ "_id" : { "a" : 2, "b" : 1 } }
{ "_id" : { "a" : 2, "b" : 2 } }
> db.foo.find({
... "_id": { "$gt" : { "b": 1 } }
... })
>
