Re: LIKE operator on indexed JSON attribute does not use index

[David Stokes]

You examples is not showing what you think it is showing. You probably want something like what follows.

create table app (id serial,
doc json,
virtual_name char(20) generated always as (doc->"$.name"),
index i (virtual_name)
);
explain select * from app where virtual_name like 'app-'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app
partitions: NULL
type: range
possible_keys: i
key: i
key_len: 81
ref: NULL
rows: 1
filtered: 100
Extra: Using where

Dave Stokes
MySQL Community Manager
Oracle Corporation