Description:
Using MultiValued Index on json array field, Json functional part(JSONPath) "$" and "$[*]" makes different result. (See "How to repeat" section)
And I have found JSONPath "$" and "$[*]" yield different results on MySQL server as following example.
mysql> select json_extract(json_array(), '$'), json_extract(json_array(), '$[*]');
+---------------------------------+------------------------------------+
| json_extract(json_array(), '$') | json_extract(json_array(), '$[*]') |
+---------------------------------+------------------------------------+
| [] | NULL |
+---------------------------------+------------------------------------+
I have read the manual which explains about "Nullability and multi-valued indexes".
https://dev.mysql.com/doc/refman/8.4/en/create-index.html#create-index-multi-valued:~:text...
What I am wondering is that following is working as designed? I want to use JSONPath "json->"$[*]" in Multi-valued Index. But result of JSONPath for empty-array is not explained in Manual. Just wondering if these behavior can be changed in future version
1) JSONPath "$[*]" for empty-array yeild NULL
1) JSONPath "$" for empty-array yeild "[]"
How to repeat:
sql> create table mvi (
id int primary key,
t int,
j json,
index index1 (t, (cast(j->'$' as signed array))),
index index2 (t, (cast(j->'$[*]' as signed array)))
);
sql> insert into mvi values (1,1,'[1,2,3]'), (2,1,'[]'), (3,1,NULL);
sql> select * from mvi force index(index1) where t=1;
+----+------+-----------+
| id | t | j |
+----+------+-----------+
| 3 | 1 | NULL |
| 1 | 1 | [1, 2, 3] |
+----+------+-----------+
2 rows in set (0.04 sec)
sql> select * from mvi force index(index2) where t=1;
+----+------+-----------+
| id | t | j |
+----+------+-----------+
| 2 | 1 | [] |
| 3 | 1 | NULL |
| 1 | 1 | [1, 2, 3] |
+----+------+-----------+
3 rows in set (0.04 sec)
