数据不从mysql json数据类型返回

Question

I have search data from json field of mysql by where condition. but there are no data show.

MySql field name of "attributes_list" which is json type

My json data:

{
 "0": 
    {
      "id": "01c93aae-8603-42f3-b097-89357467872a#####ed20eb29-611a-4e5c-a966-c09708c98179", 
      "text": "Canada", 
      "attr_name": "Country"
    }, 
 "1": 
    {
      "id": "01c93aae-8603-42f3-b097-89357467872a#####6e6269a1-afbb-4d5d-b2ee-6d25fe38fec7", 
      "text": "Ontario", 
      "attr_name": "REGION"
    }, 
 "2": 
    {
      "id": "5a0a085e-f3e4-4fbe-9f3e-61cec4836d36#####d81923c9-9532-4375-ba3d-19214ac61c4e", 
      "text": "Lager", 
      "attr_name": "TYPE"
    }, 
 "-1": {"id": "", "text": "", "attr_name": ""}
}

My json query of mysql:

when I run following query then show data

SELECT * FROM products where attributes_list->'$[0]."0".text' = 'Canada';

but when I run this there are not show data

SELECT * FROM products where attributes_list->'$[0]."*".text' = 'Canada'; 

I explain my condition:

In product table there are 6 rows. In product table, there are a field which name attribute list.

This field is json type. There are save multiple attribute of a product. my attribute_list json format is

{"0":{"id":"","text":""},"1":{"id":"","text":""}}. 

Now I want to search where text='Canada' then read those product information

Answer 1

According to mySql manual selector $**.text will come in use.

[search for (the path $**.b) on the page to reach there.]

This will search for second level of every document and will return the value of text key at second level. So your query will be like:

SELECT * FROM products where attributes_list->'$.*.text' = 'Canada';

Also I have tried to perform same task as you mentioned above in fiddle