Support cast array function to creating multi-valued index #981
Description
Description
As of MySQL 8.0.17, InnoDB supports multi-valued indexes. A multi-valued index is a secondary index defined on a column that stores an array of values.
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
To create a multi-valued index, we need cast a json path to array type and django.db.models.functions.comparison.Cast doesn't support this feature(obviously django should not support this).
As a workround, we can subclass the Cast function like this:
class CastArray(functions.Cast):
template = "%(function)s(%(expressions)s AS %(db_type)s ARRAY) "Assume we have model Journal and it has issns as JSONField(default=list) type, we can define a multi-valued index like this:
>> i = models.Index(CastArray("issns", models.CharField(max_length=15)), name="multi-value-index")
>> i.create_sql(Journal, e)
<Statement 'CREATE INDEX `multi-value-index` ON `scholardata_journal` ((CAST(`issns` AS char(15) ARRAY) ))'>
>> i.remove_sql(Journal, e)
<Statement 'DROP INDEX `multi-value-index` ON `scholardata_journal`'>I can make a PR if this feature request is accepted.