Can a partial multikey index in MongoDB index only certain subdocuments of an array?

Question

I have a MongoDB collection with documents containing an array of subdocuments. For example:

{
  "_id": 1,
  "addresses": [
    { "city": "New York", "country": "USA" },
    { "city": "Toronto", "country": "Canada" }
  ]
}

I want to create an index on addresses.city but only for subdocuments where country is "USA".

I tried using a partial index like this:

db.users.createIndex(
  { "addresses.city": 1 },
  { partialFilterExpression: { "addresses.country": "USA" } }
)

However, I’m not sure how MongoDB handles this:

1. Will it index only the addresses elements where country is "USA"?

2. Or will it index all addresses.city values in the document if any element matches the partial filter?

I’m looking for clarification on how partial multikey indexes work with arrays of subdocuments. If MongoDB cannot index only matching subdocuments, are there any recommended workarounds?

MongoDB version: 7.0+ (but any explanation about multikey + partial indexes is helpful)

Answer 1

No.

From the docs, emphasis mine:

"Partial indexes only index the documents in a collection that meet a specified filter expression. By indexing a subset of the documents in a collection..."

A document ether meets the criteria to be indexed, or not. Has nothing to do with arrays of subdocuments where "some subdocuments will be indexed and some not". There aren't any "partial-document partial indexes" or "subdocument partial indexes".

So it's the 2nd case here:

1. Will it index only the addresses elements where country is "USA"?
2. Or will it index all addresses.city values in the document if any element matches the partial filter?

Wrt

If MongoDB cannot index only matching subdocuments, are there any recommended workarounds?

Recommendations for workarounds would be opinion-based and depends entirely on your actual use-case:

1. Unwind the addresses and store them in a separate collection with a partial index.
   • You would then need lookups from the 'users' collection for all address searches.
2. Store US addresses in a separate field like us_addresses and create a partial index on us_addresses.city where us_addresses: { $ne: [] }.
3. Store US addresses in a separate field like us_addresses and create a sparse index on us_addresses.city. For the sparse index to work, make sure the us_addresses field does not exist in the document if there are no US addresses.

Answer 2

In principle an index entry has two fields:

• the identifier of the document (or the row in a RDBMS database)
• the value of the key, in your case addresses.city

Thus, it is not possible to create any kind of index on subdocument level. You always index the entire document