json - Using secondary index for .lt() with .filter() in RethinkDB

Question

Currently trying to solve a problem by using RethinkDB to find those objects which has a createdAt date key with less than the given value by using .lt().

The following query works just fine with .filter() and .lt():

r.db("databasename")
  .table("tablename")
  .filter(r.row("createdAt").lt(new Date()))

Using .between() I can pass the index as expected as:

r.db("databasename")
  .table("tablename")
  .between(new Date("2020-01-01"), new Date(), { index: "createdAt" })

But still I need to pass a lower value (2020-01-01) as well not like with .lt() which is not the same.

Question:

Even though the first query with .filter() and .lt() works as expected, only issue is it's not using the secondary index what I created.

Is there any way to use the secondary index with .lt() similarly like .between() or somehow with .filter()?

The documentation does not mention anything like that. Any help is appreciated!

Answer 1

filter doesn't use secondary indexes, between should be the best choice here. Just try using r.minval to define the lower key, then it should have the same behavior as .lt(...)

https://rethinkdb.com/api/javascript/between

You may also use the special constants r.minval and r.maxval for boundaries, which represent “less than any index key” and “more than any index key” respectively. For instance, if you use r.minval as the lower key, then between will return all documents whose primary keys (or indexes) are less than the specified upper key.

r.db("databasename")
  .table("tablename")
  .between(r.minval, new Date(), { index: "createdAt" })