Querying a compound multi-index in RethinkDB

Question

I am trying to efficiently retrieve data that is in the following format:

{
  "datetime": "1453845345493",
  "someIds": ["id2000-4", "id1000-34", "id2000-43", "id250-34"]
}

Specifically, what I want to do is "find all records that have happened since a given time, and of those, return any that have one or more of a list of Ids."

So far I followed the method shown here for creating a compound, multi-index using the following:

r.db("dbName").table("tableName")
  .indexCreate(
    "idDatetime", 
    function(each) {
      return each("someIds").map(function(id){
        return [each("datetime"), id]
      })
    }
    ,{multi: true})

This successfully builds an index based on values that look like ["1453845345493", "id2000-4"]

But now it feel like I'm in a bit too deep, and don't actually know how to make a query that uses this index to accomplish the objective above. How do you craft that query?

Answer 1

I think(I maybe wrong), basically, we have two types of index lookup:

• exactly match: get, getAll
• range match: between

So in your case, it's clearly we cannot use getAll because you want to find all records that have happened since a given time, and of those, return any that have one or more of a list of Id.

That leave us only between. So let's find a way to model it.

I suggest to change datetime field to numeric instead of string. I guess you are storing epoch.

We will create index as you did:

r.table('t11').indexCreate('idDatetime', function(doc) {
  return doc('someIds').map(function(id){
        return [doc("datetime"), id]
  })
}, {multi: true})

Then we query it similar to this:

r.table('t11')
 .between([1453845345493, "id1000-34"], [r.maxval, "id1000-34"], {index: 'idDatetime'})

To find all the document since that epoch time and contains id1000-34. You can find the epoch of yesterday using either JavaScript or RethinkDB datetime function.

---

UPDATE:

While it isn't perfect, We can simulate either id with sth like this:

r.expr(["id1000-34", "id1000-4"])
  .concatMap(function(needle) {
    return r.table('t11')
     .between([1453845345499, needle], [r.maxval, needle], {index: 'idDatetime'})
  })
  .distinct()