Multi and compound secondary index with two numbers in RethinkDB

Question

I have documents in an otherwise empty RethinkDB table called numbers below containing nested objects. I create a secondary index, a compound and a multi index, on a pair of numbers inside the inner object. Compound because I want to use 2-element arrays as secondary indexes, and multi because the same 2-pair might correspond to multiple documents.

Here, I create that secondary index and insert some documents in the Data Explorer browser view:

r.db('test')
    .table('numbers')
    .indexCreate('idx', [ r.row('group')('a'), r.row('group')('b') ],
                 {multi : true});
r.db('test').table('numbers').insert([
  {name : 'Foo', group : {a : 2, b : 3}},
  {name : 'Bar', group : {a : 2, b : 9}},
  {name : 'Baz', group : {a : 2, b : 3}},
  {name : 'Qwer', group : {a : 4, b : 4}}
]);

Now I go to run a query using between. For this example, I expect to see three documents returned, but instead I get no documents back:

r.db('test').table('numbers').between([ 2, 0 ], [ 3, 5 ], {index : 'idx'});
//  No results returned.

Confused, I tested this secondary index with just scalar lookups (so pretending idx isn't a compound index): that returns documents!

r.db('test').table('numbers').between(2, 3, {index : 'idx'})
//  3 rows returned: Foo, Bar, Baz

What? Why would treating the idx secondary array as a scalar index (rather than arrays) and return three documents? What happened to my compound index?

Setting rightBound to closed doesn't seem to do anything. Also, building the index key using a function, i.e., passing function(obj) {return [obj('group')('a'), obj('group')('b')];} to indexCreate didn't make a diference.

I've tested another compound multi-index whose keys are [string, number, number], and between works great for that multi case: it finds documents. Why won't the two-number multi case work here?

Answer 1

I think the main problem here is that you don't need a multi index.

Understanding Multi Indexes

multi because the same 2-pair might correspond to multiple documents

With any secondary index, the index presumes that the value of that property (in this case, the pair of values) corresponds to multiple documents. In RethinkDB, because it's a distributed database, you can only guarantee that a value will be unique for the primary index.

Multi indexes are intended for an array of values for which you want one of those values. Tags are an example of this:

{
    "title": "...",
    "content": "...",
    "tags": [ <tag1>, <tag2>, ... ]
}

// Create the multi index based on the field tags
r.table("posts").indexCreate("tags", { multi: true })

// Your query
r.table("posts").getAll(<tag1>, { index: "tags" })

Your Query

Because of this, your can just create the same compound index without it being a multi index.

r.db('test')
 .table('numbers')
 .indexCreate('idx', [ r.row('group')('a'), r.row('group')('b') ]);

Then your query would return as expected:

r.db('test').table('numbers').between([ 2, 0 ], [ 3, 5 ], {index : 'idx'});

Returns:

{
  "group": {
    "a": 2 ,
    "b": 3
  } ,
  "id":  "f711dae4-7e91-4864-9977-956221a10a08" ,
  "name":  "Foo"
}, {
  "group": {
    "a": 2 ,
    "b": 3
  } ,
  "id":  "29732f5f-a6e6-45e6-aaaf-fd2c9d1bb3fe" ,
  "name":  "Baz"
}, {
  "group": {
    "a": 2 ,
    "b": 9
  } ,
  "id":  "1881eafb-4349-43c8-9fb2-d453720b09a6" ,
  "name":  "Bar"
}