I have a sample table as below
| GROUP_ID | MEMBER ( Data type is CLOB and constraint check is JSON)
|
|---|
| 1 | ['Jone','Garry'] |
| 2 | ['Nick','Bill'] |
| 3 | ['Bill','Garry'] |
| 4 | ['Nick','Lisa'] |
It has been transformed from JSON format and I need to create index on MEMBER column to run following SQL
select group_id,member
from group_list gl,
json_table(t.member,'$[*]' columns (member_list path '$')) ga
where ga.member_list like '%Nick%';
Currently, the explain plan is a full-scan table, I'm trying to avoid by creating index on "MEMBER" column by using json_value but I got error message
ORA-40470: JSON_VALUE evaluated to multiple values
How do I create an index on the "MEMBER" column?