mysqlarraysjsonindexingmultivalue

How to create index on sub field of json arry in mysql?


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?


Solution

  • 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