An index can be created on array elements either using multi-value index or index on virtual column. Is it possible to create index on a sub field of the array elements? e.g.

create table link
(
    id bigint not null primary key,
...
    left json not null
)

structure of column left:

[{
  "fieldId": 123, ...
}]

Multi-value index: can be created but not used when query

ALTER TABLE link ADD INDEX IDX_LEFT_FIELD( (CAST(`left`->'$[*].fieldId' AS unsigned ARRAY)) );

Virtual column: can be created but column left_field_id on each row produces value 0 instead of the value of sub field fieldId

ALTER TABLE link
ADD COLUMN `left_field_id` bigint GENERATED ALWAYS AS (`left`->'$[*].fieldId') Virtual NULL AFTER `left`;

I wonder if there is anything wrong in the statements above?

CodePudding user response：

As I see the index is used.

DEMO

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	link	null	range	IDX_LEFT_FIELD	IDX_LEFT_FIELD	9	null	5	100.00	Using where