How to add collation in indexes for mongodb?

Question

I work with Mongoengine in Django, got task to sort by first_name, last_name fields ignoring case and leading whitespaces

that is why add collation in queryset:

collation = dict(
    locale='en',
    caseLevel=False,
    caseFirst='off',
    strength=3,
    numericOrdering=True,
    alternate='shifted',
    maxVariable='space',
    backwards=False,
)

return queryset.collation(collation).order_by('-is_stocked', 'brand', 'model')

Unfortunetly, my queryset takes too long time

I want to speed up it, so start to read about mongodb indexes, but don't understand how add it properly, I tried this: models.py

class Car(UpdatedAtTimestampMixin, Document):
    model = fields.StringField(null=True, db_field='model')
    brand = fields.StringField(null=True, db_field='brand')
    is_stocked = fields.BooleanField(db_field='isStocked')

    meta = {
        'collection': 'books',
        'strict': False,
        'indexes': [
            ['-is_stocked', 'brand', 'model'],
        ],
    }

The main question is How to include collation in indexes? And will it work with null=True Field?

this maybe of help: https://stackoverflow.com/questions/22931177/case-insensitive-sorting-in-mongodb and here is how to create case insensitive index: https://www.mongodb.com/docs/manual/core/index-case-insensitive/ — R2D2, Apr 01 '22 at 11:47
for the leading spaces is best to sanitize the data , or $LTRIM later ... — R2D2, Apr 01 '22 at 11:50

score 0 · Answer 1 · answered Apr 21 '22 at 08:06

0

class Car(UpdatedAtTimestampMixin, Document):
    model = fields.StringField(null=True, db_field='model')
    brand = fields.StringField(null=True, db_field='brand')
    is_stocked = fields.BooleanField(db_field='isStocked')

    meta = {
        'collection': 'books',
        'strict': False,
        'indexes': [
            {'fields': ['-is_stocked', 'brand', 'model'], 'collation': collation},
        ],
    }

answered Apr 21 '22 at 08:06

autoescape_on

1
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 21 '22 at 08:29

How to add collation in indexes for mongodb?

1 Answers1