How do I index two arrays in MongoDB?

Question

db.hello.ensureIndex({"array1":1, "array2":1})

MongoDB does not allow that, because they say "it can get out of hand". However, I know that my arrays will never exceed length of 3. How can I hack MongoDB to allow indexing multiple arrays at once?

When using a compound index, at most one of indexed values in any document can be an array. So if we have an index on {a: 1, b: 1}, the following documents are both fine:

{a: [1, 2], b: 1} {a: 1, b: [1, 2]} This document, however, will fail to be inserted, with an error message "cannot index parallel arrays":

{a: [1, 2], b: [1, 2]} The problem with indexing parallel arrays is that each value in the cartesian product of the compound keys would have to be indexed, which can get out of hand very quickly.

Are you sure you need the index on both arrays? If you have a high selectivity already on one of them, that may be good enough. — Thilo, Jun 29 '11 at 06:50
@Thilo ... the first array has about 100 elements usually. The 2nd array has about 2-3 elements. Do you think I need to index the 2nd array? — TIMEX, Jun 29 '11 at 07:02
@TIMEX Don't index the 2nd array and see how it performs using the explain command. — Joe, Jun 29 '11 at 08:41
@Joe what if I don't have any data right now (the database is just for testing). — TIMEX, Jun 29 '11 at 09:41
How many distinct values are in the two fields? If filtering on just one of the indexes eliminates already 95% of the documents, that could be enough. — Thilo, Jun 29 '11 at 09:50
Additionally you could store an array of a_b instead of a and b as seperate arrays (e.g. [1_1, 1_2, 2_1, 2_2] instead of [1,2], [1,2]) — Remon van Vliet, Jun 29 '11 at 10:44
@Joe, @Thilo, @Remon Thanks for all the comments. The first array is a list of facebook friends (about 200 average?). The second array is the first and last names of people...['Jennifer','smith']. In other words, I want to look up people who are in my friends_list and and also match their name. — TIMEX, Jun 29 '11 at 11:16

score 13 · Accepted Answer · edited Jul 23 '19 at 06:08

The short answer to your question is; you don't. The only option available to you is to store the every unique pair as a single array element. So rather than :

{a:[1,2], b:[8,9]}

you store

{ab:[[1,8], [1,9], [2,8], [2,9]]}

Obviously this has a few downsides so it really depends on your specific usecase whether or not this is an appropriate workaround. I do agree however that mongo shouldn't reject multiple array indexes just for idiot proofing. It's a good feature for small/low cardinality arrays.

How do I index two arrays in MongoDB?

1 Answers1

Linked