Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Use MongoDB _id field as composite field with multiple fields

Since every collection in mongodb has a default index on the _id column, I wanted to leverage it for my scenario as below.

I have my collection as below,

{
    "_id":{
            "timestamp" : ISODate("2016-08-24T23:22:20.201Z"),
            "departmentname" : "sales",
            "city":"NJ"
        }

        //Other fields in my collection
}

With this structure I am able to query as below,

db.getCollection('test').find(
{
    "_id" : {
        "timestamp" : ISODate("2016-08-21T23:22:20.201Z"),
        "departmentname" : "sales",
        "city":"NJ"
    }
}
)

But, when I query by one or more fields in which are part of _id column as below,

db.getCollection('test').find(
{
    "_id" : {
        "timestamp" : ISODate("2016-08-21T23:22:20.201Z")
    }
}
)

(OR)

db.getCollection('test').find(
{
    "_id" : {
        "departmentname" : "sales"
    }
}
)

(OR)

db.getCollection('test').find(
{
    "_id" : {
        "departmentname" : "sales",
        "city":"NJ"
    }
}
)

I do not see any documents returned

When I checked with .explain() I see that it has used Index but did not find any documents.

Also, I would like to do date range queries on timestamp field along with query on one or more fields in the _id column like below,

db.getCollection('test').find(
{

    "_id.timestamp" : { 
        "$gte": ISODate("2011-08-21T23:22:20.201Z")
    },
    "_id.departmentname" : "sales"
}
)

But, I do not see any documents returned. When I run .explain() I see it has used colscan and not index.

Can someone help me on the right way to query by one or more fields on my _id column.

Thanks,

Sri

like image 547
javauser Avatar asked Mar 11 '23 08:03

javauser


1 Answers

You can try following query, in first case:-

db.getCollection('test').find(
{
    "_id.timestamp" :  ISODate("2016-08-21T23:22:20.201Z")
})

And this for multiple fields:

db.getCollection('test').find(
{
    "_id.timestamp" :  ISODate("2016-08-21T23:22:20.201Z"), 
    "_id.departmentname" :  "sales", 
})
like image 156
Shrabanee Avatar answered Mar 14 '23 17:03

Shrabanee