[SERVER-48570] Allow wildcard indexes to be compounded with preceding fields Created: 03/Jun/20  Updated: 29/Oct/23  Resolved: 20/Mar/23

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: None
Fix Version/s: 7.0.0-rc0

Type: New Feature Priority: Major - P3
Reporter: Ralf Strobel Assignee: Ruoxin Xu
Resolution: Fixed Votes: 15
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
is documented by DOCS-15965 [Server] Allow wildcard indexes to be... Closed
Related
Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Sprint: QO 2023-04-03
Participants:

 Description   

Wildcard Indexes currently do not support compounding in any fashion. A preceding compound field would support additional use cases.

Consider a collection containing polymorphic documents that share a common "type" field but may otherwise contain varying content fields...

{
    "_id": <ObjectId>,
    "type": <int>,
    "content": {
        "field1": <string>,
        "field2": <string>
        ....
    }
}

In such cases, many queries will want to match documents based on a content field, but only within a given target type. Hence it would be preferable to create the following index:

db.coll.createIndex({ "type": 1, "content.$**": 1 })

Which is currently not permitted and will be answered with a generic "wildcard indexes do not allow compounding" error message.

MongoDB currently scales better with few large collections and few indexes than it does with many small collections and many indexes (WT-5479). Given that one of the major advantages of MongoDB is that it is schema-free, building polymorphic collections seems like the natural way to go. More flexible wildcard indexes could provide key support in this regard.



 Comments   
Comment by Ruoxin Xu [ 23/Mar/23 ]

Hi all! This new feature has been implemented and will be delivered in 7.0. In 7.0, we can compound the wildcard field with other regular fields to create a compound wildcard index. The wildcard field can be in an arbitrary position in the key pattern, for examples,

  • {“attributes.$**”: 1, tenantId:1, name: 1}
  • {tenantId:1, “attributes.$**”: 1, name: 1}
  • {tenantId:1, name: 1, “attributes.$**”: 1}

The option, ‘wildcardProjection’, supported in the regular/single-field wildcard index is still supported in a compound wildcard index. We also extend wildcard indexes to support specifying either ascending or descending sort order of any field in a (compound) wildcard index.

Comment by Katya Kamenieva [ 07/Sep/22 ]

Thanks to everyone for adding their votes and comments. This item is on the roadmap, but there is no target release date to share yet.

Comment by Renner Garcia [ 07/Sep/22 ]

 Are there any plans to resolve this in the near future?

Comment by Paul Robinson [ 21/Sep/21 ]

We really need this at our company. I wouldn't mind if it just offered 1 extra field, that could be an improvement just by itself.

Comment by Loris Guignard [ 06/Nov/20 ]

This is required for any SaaS using MongoDB with a multi-tenant architecture and dynamic schema features. Really looking forward for this one. Thanks!

Comment by Ralf Strobel [ 10/Jun/20 ]

I will definitely give you some credit for coming up with this solution. But yeah, it is a workaround (to avoid the word "hack"). This does not seem like a way I would actually like to re-schematize all of our customer databases.

Your solution might be workable in the situation with distinct tenants. But I intentionally used the word "polymorphism", as in there may actually be a complex system of types and subtypes or (traits), where some types may share certain fields while others don't. And yeah then you could go ahead and use explicit $or-Queries whenever you want a multi-type query and then pick the right sub-document from each result etc. But there it gets very messy very quick.

So no, my feature request still stands.

Comment by Asya Kamsky [ 09/Jun/20 ]

ralf.strobel note that there is a workaround for cases where you want a leading field (like type or tenant id) before the wildcard subdocument index, it's described here: https://www.mongodb.com/blog/post/wildcard-indexes-and-multitenant-deployments

You can use a similar approach by making type a key name of the subdocument content

 

Comment by Ralf Strobel [ 09/Jun/20 ]

I just stumbled upon SERVER-28743, which effectively does something quite similar on a deeper storage level. Are there still plans to roll out this alternate storage mode and is it already compatible with wildcard indexes?

Comment by Carl Champain (Inactive) [ 08/Jun/20 ]

Hi ralf.strobel,

Thank you for the report.
We're passing this ticket along to the appropriate team for additional investigation. Updates will be posted on this ticket as they happen.

Kind regards,
Carl

Generated at Thu Feb 08 05:17:30 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.