Add Index on a query using JSON_GET or other methods

[alexander-schranz]

More a question. I want to add a index on a specific JSON query I know its possible via PostgreSQL the query looks like this:

$queryBuilder->leftJoin(
    Contact::class,
    'contact',
    Join::WITH,
    'CAST(JSON_GET(JSON_GET(visit.data, \'code\'), \'contactId\') AS INT) = contact.id',
)

It is a third party entity and I wanted to use Events::loadClassMetadata to add a index but the following:

$builder = new ClassMetadataBuilder($metadata);
$builder->addIndex(['CAST(JSON_GET(JSON_GET(visit.data, \'code\'), \'contactId\') AS INT)'], 'data_code_contactId_idx');

because it is not a column maybe somebody achieved something like this, without that the ORM is throwing the index away on the next migration.

[Hikariii]

This falls outside what this library can fix.

The library supplies DQL query functions. It owns no code in schema generation or migrations.

An index over an expression, such as JSON_GET(data, 'key'), is a schema concern. Doctrine models an index over real column names through Doctrine\DBAL\Schema\Index, so an expression index gets dropped on the next schema diff. No node or parser change here affects that.

Two routes exist for a consumer today:

• create the index with custom SQL in a migration, and exclude it from schema comparison
• on MySQL, add a generated column over the JSON path and index that column
• on PostgreSQL, create a GIN index in a migration

A general fix belongs upstream in doctrine/orm or doctrine/dbal, as functional index support.

Suggest closing with this explanation.