CODEWITHSUNDEEP
mongodbindexinggeospatialcompound-index
R_User
R_User

Reputation: 11082

How to find documents having a query-value within the range of two key-values

I'm analyzing texts. Those texts have annotations (e.g. "chapter", "scenery", ...). Those annotations are in my MongoDB collection annotations, e.g.

{
  start: 1,
  stop: 10000,
  type: chapter,
  details: {
    number: 1,
    title: "Where it all began"
  }
},
{
  start: 10001,
  stop: 20000,
  type: chapter,
  details: {
    number: 2,
    title: "Lovers"
  }
},
{
  start: 1,
  stop: 5000,
  type: scenery,
  details: {
    descr: "castle"
  }
},
{
  start: 5001,
  stop: 15000,
  type: scenery,
  details: {
    descr: "forest"
  }
}

Challenge 1: For a given position in the text, I'd like find all annotations. For example querying for character 1234 should tell me, that

Challenge 2: I also like to query for ranges. For example querying for characters form 9800 to 10101 should tell me, that it touches chapter 1, chapter 2 and the scenery forest.

Challenge 3: Comparable to challenge 2 I'd like to match only those annotations that are completely covered by the query-range. For example querying for characters form 9800 to 30000 should only return the document chapter 2.

For challenge 1 I tried to simply use $lt and $gt. e.g.:

db.annotations.find({start: {$lt: 1234}, stop: {$gt: 1234}});

But I realized, that only indexes for the key start is used, even if I have a compound index for start and stop. Is there a way to create more adequate indexes for the three problems I mentioned?

I shortly thought of geospatial indexes, but I haven't used them, yet. I also only need a one-dimensional version of it.

Upvotes: 1

Views: 1687

Answers (1)

Juan Carlos Farah
Juan Carlos Farah

Reputation: 3879

For Challenge 1, the query you are using is appropriate, though you might want to use $lte and $gte to be inclusive.

db.annotations.find({ "start": { "$lt": 1234 }, "stop": { "$gt": 1234 }});

Regarding indexes, the reason it chooses to use the index on start instead of the compound index has to do with the tree structure of compound indexes, which is nicely explained by Rob Moore in this answer. Note that it can still use the compound index if you use hint(), but the query optimiser finds it faster to use the index on start and then weed out the results that don't match the range for the stop clause.

For Challenge 2, you just need to use an explicit $or clause to cover the cases where stop is within the bounds, when start is within the bounds and when start and stop encompass the bounds.

db.annotations.find({
    "$or": [
        { "stop": { "$gte": 9800, "$lte": 10101 }},
        { "start": { "$gte": 9800, "$lte": 10101 }},
        { "start": { "$lt": 9800 }, "stop": { "$gt": 10101 }}
    ]
});

For Challenge 3, you can use a query very similar to the one in Challenge 1, but ensuring that the documents are completely covered by the given bounds.

db.annotations.find({ "start": { "$gte": 9800 }, "stop": { "$lte": 30000 }});

Upvotes: 3

Related Questions