skip to content

Why does MongoDB refuse to index two array fields in one compound index?

level: seniorimportance: should knowfreq 46%

answer

  1. Count the keys two arrays would need together
  2. Multiplication, not addition
  3. One document may only supply one array per index
  4. The error can appear long after the build
  5. Restructure into one array of subdocuments

basics

~20 s

Because the index would have to store the cross product of both arrays: a document with 10 and 20 elements would generate 200 keys. MongoDB rejects such parallel arrays — the index build fails, or the offending write is rejected once the index exists.

solid answer

~50 s

A multikey index writes one key per array element. If two indexed fields in the same document were both arrays, the index would need every combination of their elements — the cross product — so key count would multiply rather than add, and a couple of moderately sized arrays would explode into thousands of keys per document. MongoDB refuses instead: if existing documents already have arrays in both fields, `createIndex` fails; if the index already exists, the insert or update that would create parallel arrays is rejected with a write error. A compound index may still be multikey, just on **at most one** of its fields per document — `{ customerId: 1, "items.sku": 1 }` is fine. The related restrictions worth knowing are that a hashed index cannot be created over an array field, a shard key field cannot hold an array, and a query needing the array field itself cannot be covered by the index.

code

javascript · 4 lines
javascript
// rejected: both indexed fields are arrays in this document
db.orders.createIndex({ tags: 1, "items.sku": 1 })
db.orders.insertOne({ tags: ["a", "b"], items: [{ sku: "A1" }, { sku: "B4" }] })
// write error: cannot index parallel arrays

go deeper

for a junior

Recall the flat rule: a compound index may cover at most one array field per document, and MongoDB rejects documents that break it.

for a middle

Explain the cross-product arithmetic behind the rule and note that the check is per document, so the index build and later writes can both fail.

for a senior

Diagnose the production case where an index built cleanly and writes began failing later, and propose a restructuring such as one array of subdocuments carrying both attributes.

for a principal

Own the design stance: two unbounded arrays in one document that both need indexing is a modelling error, and the schema should split before the index restriction forces the issue.

## The cross-product problem A multikey index stores one key per array element. Extend that to two array fields in a single compound index and the arithmetic changes shape: to answer a query filtering on both fields, the index would need an entry for every *pair* of elements. A document with 10 tags and 20 line items would need 200 index keys; 100 and 100 would need 10,000. Key count would grow multiplicatively with array sizes that the schema does not bound, so a single write could produce an arbitrary amount of index work. MongoDB does not attempt this. It rejects the situation outright. ## How the rejection shows up The error surfaces at two different moments, and knowing which one you are looking at is the practical part: - **At index build.** If any existing document already has arrays in both indexed fields, `createIndex` fails with a parallel-array error. On a large collection this can happen minutes into a build, which is why you check the data shape first. - **At write time.** If the index built successfully — because no document happened to have both arrays — the restriction is still live. A later insert or update that would put arrays in both fields of that index is rejected with a write error. The document is not saved. This second case is the nasty one: an index that built cleanly in staging can start rejecting production writes when a document shape that was merely absent, not impossible, finally appears. That is an argument for validating the schema shape rather than relying on the index build as proof. ## What is still allowed A compound index may absolutely be multikey — on one field. `createIndex({ customerId: 1, "items.sku": 1 })` is a normal, useful index: `customerId` is a scalar, `items` is an array, and the document contributes one key per line item. The restriction is per document, not per index definition: the index tolerates any mixture of documents as long as no single document supplies arrays for two of its keys. ## The neighbouring restrictions Multikey brings a small family of limitations that interviewers group together: - **Hashed indexes and arrays do not mix.** A hashed index cannot be created over an array field, and inserting an array into a field with a hashed index errors. Hashing is defined on a value, not a set of them. - **A shard key field cannot be an array.** Sharding needs one deterministic key value per document to decide placement, and an array offers several. - **The multikey flag is sticky.** Once set, it is not cleared by deleting or rewriting the array data; only dropping and rebuilding the index clears it. The planner keeps applying the more conservative multikey rules until then. - **Covering is limited.** A query whose projection needs the array field cannot be satisfied from the index alone, because the index holds the individual elements rather than the original array. - **Bounds can be loose.** With two bounds against the same array field, different elements may satisfy different halves of the predicate, so the index cannot always narrow to one tight contiguous range and the remainder is re-checked against documents. ## Designing around it When you genuinely need to filter on two independent collections of values in one document, the options are: index them separately and let the planner use one index with a residual filter; restructure so the two arrays become one array of subdocuments carrying both attributes, which makes a compound index over `arr.a` and `arr.b` a single-array case; or move one side out into its own collection. The last of these is the honest answer when both arrays are unbounded — an index over an unbounded cross product was never going to be the right structure. ## Answering well Lead with the cross-product explanation rather than "it's not allowed," then distinguish the build-time failure from the write-time rejection, and finish with the restructuring option — an array of subdocuments holding both fields — because that shows you can design past the limitation instead of just reciting it.

  • The index built fine in staging but production writes started failing weeks later. How?
    The restriction is enforced per document. If no document in staging happened to carry arrays in both indexed fields, the build succeeded, but the rule stays live: the first write that would place arrays in both fields is rejected. Nothing changed about the index; a document shape that was merely absent finally appeared.
  • How would you model data that genuinely needs filtering on two sets of values in one document?
    Fold them into a single array of subdocuments carrying both attributes, so a compound index over the two subfields is a single-array case rather than parallel arrays. Otherwise index one field and accept a residual filter on the other, or split the second collection into its own documents when it is unbounded.
  • Can a hashed index be created over an array field?
    No. Hashing is defined on a single value, not on a set of them, so a hashed index cannot be created over an array field and inserting an array into such a field errors. The same reasoning explains why a shard key field cannot hold an array: sharding needs one deterministic key value per document to decide placement.

saying these in an interview costs you the question

  • Says a compound index can never be multikey
  • Thinks the restriction is checked only when the index is created
  • Claims MongoDB silently indexes the cross product anyway
  • Believes dropping the array data clears the multikey flag
  • Suggests a hashed index as the way to index an array field

context

resolving links…