1

I have a mongodb collection in which document is stored in the below format

{
    "_id": {
        "host_ip": "192.41.15.161",
        "date": "2020-02-02T08:18:09.207Z"
    },
    "path": "/apache_pb.gif",
    "request": "GET /apache_pb.gif HTTP/1.0",
    "status": 200
}

where "host_ip" and "date" should be composite primary key i.e. (unique together) and there exists an _id index, which I think is created based on these two fields

So, how can I query based on host_ip and date together so that the "_id" index can be utilized?

Tried using db.collection.find({_id: {host_ip: "192.41.15.161", date: {$gte: ISODate('2020-02-02T08:00:00:00.000Z')}}}), but it does not work, it does not even return the record which should match. Is it not the correct way to query?

Query like db.collection.find({"_id.host_ip": "192.41.15.161", "_id.date": {$gte: ISODate('2020-02-02T08:00:00:00.000Z')}}), worked but it does not use the index created on "_id"

Nishit Jain
  • 1,499
  • 8
  • 20
  • 29

1 Answers1

2

When querying for an _id composite primary key, mongo seems to only look for exact matches (essentially treating _id like a string), so when you query for _id: {a: x, $gte: y} it looks for "{a: x, $gte: y}" and doesn't allow querying by any portion of the object.

> db.test.insert({_id: {a: 1, b: 2}});
WriteResult({ "nInserted" : 1 })
> db.test.find({_id: {a: 1}}); // no results
> db.test.find({_id: {a: 1, b: {$eq: 2}}}); // no results

As far as solving your problem goes, I'd probably set up a new unique compound index on the fields you care about & ignore the _id field entirely.

MongoDB and composite primary keys has a few more details.

willis
  • 2,637
  • 1
  • 7
  • 21
  • Thanks, I think then taking option 2 in given link (selected answer) would be a better option for me as _id index will be large because of it being composite field, as is of no use. While creating a compound index on two seperate fields (host_ip and date) would be a better option – Nishit Jain Feb 06 '20 at 05:55