Add a way to define compound indexes/indexes with partialFilterExpression/etc

[AlexeyBond]

We're currently considering migration of a relatively huge (few millions of documents) database from Strapi to Payload.
The way Payload works with MongoDB seems to be more efficient (especially in case of relations and nested documents).
However there are some issues that may become critical.

One of issues that don't seem to be solvable without modifying Payload's source code is - there is no way to define a index with a partialFilterExpression, on which we rely in some cases.
It would also be nice to be able to define compound indexes in code.

I suggest to extend collection/field configs to support like one in the following example:

const MyCollection: CollectionConfig = {
  fields: [
    ...,
    {
      name: 'foo',
      type: 'text',
      ...,
      // along with a boolean values accept objects containing index settings
      // looks like this even may work now, except there is no support in typings
      index:  {
        unique: true,
        partialFilterExpression: {
          foo: /.+/
        },
      },
    },
  ],
  ...,
  // optional array of settings for additional indexes, including compound
  indexes: [
    {
      fields: { foo: 1, bar: -1 },
      options: { unique: true },
    },
  ],
};

[DanRibbens]

This is a great feature request! I have thought about this before actually and was waiting for somebody to ask.

I can definitly see us doing that and I like your suggestion on how to extend the config. If you are interested in contributing we would gladly accept a PR. We do all our indexes in src/mongoose/buildSchema.ts.

We can give more direction too, let us know.
Thanks!

[AlexeyBond]

Hi. Thanks for the response!

I think I will be able to implement this feature if migration of our project to Payload will be approved by our managers.

[thanaParis]

Was there ever any progress on this? @DanRibbens ? Facing a similar issue of moving from one CMS to another and finding a lack of compound index/uniqueness support in Payload CMS.