How do I intersect multiple secondary index queries

Question

If I have a table with multiple secondary indexes, how do I generate an intersection? For example, if I have a users table with secondary indexes on "firstName" and "lastName", and want all users named 'Bob Smith':

r.table('users').getAll('Bob', {index: 'firstName'}).XXXX('Smith', {index: 'lastName'})

I can use a filter, but my understanding is that would be slower:

r.table('users').getAll('Bob', {index: 'firstName'}).filter({'lastName': 'Smith'})

Alternatively, can I do intersections with a compound index?

score 1 · Accepted Answer · answered Oct 29 '13 at 17:16

1

The correct way to do this is with a compound index. For example:

table.index_create("fullName", lambda doc: [doc["firstName"], doc["lastName"])
table.getAll(["Bob", "Smith"])

You can't currently use more than one index in a single query. It's something that Rethink might support in the future but right now it doesn't.

answered Oct 29 '13 at 17:16

Joe Doliner

2,058
2
15
19

2

Thanks. I believe the getAll should be getAll(["Bob", "Smith"], {index: 'fullName'}) – bjnortier Oct 30 '13 at 18:05

score 0 · Answer 2 · answered Apr 15 '16 at 14:41

The correct updated way to do this is creating a compound secondary index based on the first_name and last_name attributes. Example updated in JS: r.table("users").indexCreate( "full_name", [r.row("last_name"), r.row("first_name")] ).run(conn, callback)

Then your query will be something like Get all users whose last name is Smith and the first name name is John. r.table("users").getAll(["Smith", "John"], {index: "full_name"}).run(conn, callback)

How do I intersect multiple secondary index queries

2 Answers2

Linked