Indexes on array field keys
Indexes can be made on keys that hold arrays as values.
If a key/val in a document looked like
genres: ["Rock", "Pop","Metal"]
the server would create index keys for each item in the array.
For the above example, the db would create indexes on
- categories +
Rock
- categories +
Pop
, - categories +
Metal
With multi-key indexes, Indexes can also deal with nested arrays & nested docs. Could make an index on the below arr of objs, & the db would make index keys on the color
, weight
, and name
keys.
gear: [
{color: 'orange', weight: .1, name: 'Jacket'},
{color: 'green', weight: 8, name: 'Water Bottle'},
{color: 'green', weight: 4, name: 'Pack'}
]
As an example, with an index on {"gear.weight": 1}
, queries could leverage the weight
to sort nested val results.
Restrictions
Avoid multiple array fields
Avoid multiple index keys on multiple array key/values.
For each indexed doc, at most 1 indexed field can be an array.
Can not make an index on 2 fields that are both arrays.
If a compound index were created across 2 fields that both hold arrays, a cartesian product
would be used to create a LOT of indexes.
Attempting to insert an array on an indexed object field will throw an error, something like cannot index parallel arrays [new erroneous key name here] [existing functional array key name here]
Covered Queries
MultiKey Indexes do not support covered queries.
Trying to use covered queries will not work here.
MultiKey Indexes from the shell
MongoDB recognizes multi-key indexes where a sub-doc is an array.
Compound Multi-Key indexes are still ok
# productName holds a string val
db.products.createIndex({productName:1, "gear.weight": 1})
From the shell
use m201
// create a document
db.products.insertOne({name: "Hipster T-Shirt", categories: ["T-Shirt", "Clothing", "Hipster"], stock: { size: "L", color: "gray", quantity: 87 }})
// create nested index
db.products.createIndex({"stock.quantity": '1'})
// create exp obj
const e = db.products.explain()
// show query performance
e.find({"stock.quantity": 87})
- isMultiKey will show false
- mongoDB only recognizes a multi-key index when the field value is an array in at least 1 doc in the collection