DEV Community

Saqib Ameen Subhan
Saqib Ameen Subhan

Posted on

cannot index parallel arrays, the MongoDB error that improves your schema

#mongodb #indexing #datamodeling #database 
MongoServerError: cannot index parallel arrays [certifications] [skills]
Enter fullscreen mode Exit fullscreen mode

If you've never hit this error, your schemas have been lucky. A team once brought me a document shaped like this during a design review:

{
  _id: 1,
  name: "engineer-42",
  skills:         ["mongodb", "cassandra", "python"],
  certifications: ["dba", "atlas-admin"]
}
Enter fullscreen mode Exit fullscreen mode

They wanted fast queries on skill and certification combinations, so the natural move was:

db.engineers.createIndex({ skills: 1, certifications: 1 })
// MongoServerError: cannot index parallel arrays
Enter fullscreen mode Exit fullscreen mode

MongoDB refuses. Not "performs badly", refuses. And the reason is one of those internals that, once you see it, changes how you model.

Why: multikey indexes are per element

When you index an array field, MongoDB creates a multikey index, one index entry per array element. Index skills on the document above and you get three entries, one for "mongodb", one for "cassandra", one for "python", each pointing at the same document.

Now imagine a compound index across two independent arrays. To answer any combination query, MongoDB would need an entry for every element of the cross product. Three skills times two certifications is six entries. Harmless here. But two arrays of 1,000 elements each is a million index entries for a single document. One insertOne fans out into a million B-tree writes. The server declines to let you build that footgun, and honestly, good.

There's a quieter problem hiding in the schema too. The combinations are meaningless. Nothing in two parallel arrays says which skill relates to which certification. The data model can't answer the question the index was supposed to serve.

The fix: one array of subdocuments 

{
  _id: 1,
  name: "engineer-42",
  quals: [
    { skill: "mongodb",   cert: "dba" },
    { skill: "mongodb",   cert: "atlas-admin" },
    { skill: "cassandra", cert: null }
  ]
}
Enter fullscreen mode Exit fullscreen mode 
db.engineers.createIndex({ "quals.skill": 1, "quals.cert": 1 })
// works
Enter fullscreen mode Exit fullscreen mode

This is allowed because both indexed paths traverse the same array. Index entries are generated per array element, so each entry is a real (skill, cert) pair that actually existed together in the data, the correlation the parallel arrays destroyed. The index gets smaller and the model gets more truthful. That combination is rare.

One caveat worth knowing before you rely on it. To match both fields within the same element, query with $elemMatch:

db.engineers.find({ quals: { $elemMatch: { skill: "mongodb", cert: "dba" } } })
Enter fullscreen mode Exit fullscreen mode

Without $elemMatch, MongoDB matches documents where some element has the skill and some other element has the cert. The parallel arrays bug, reincarnated at query time.

The lab 

db.t.insertOne({ a: [1,2,3], b: ["x","y"] })
db.t.createIndex({ a: 1, b: 1 })                          // watch it fail

db.t2.insertOne({ pairs: [{a:1,b:"x"},{a:2,b:"y"}] })
db.t2.createIndex({ "pairs.a": 1, "pairs.b": 1 })         // watch it work

db.t2.find({ pairs: { $elemMatch: { a: 1, b: "y" } } })   // zero results, and that's correct
Enter fullscreen mode Exit fullscreen mode

That last line is the whole lesson in one query. a:1 and b:"y" never occurred in the same element, so the document doesn't match. Parallel arrays would have happily lied to you.

Some errors are the database being difficult. This one is the database refusing to let your schema make a promise it can't keep.

Top comments (0)