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

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

我有来自mysql的json字段的搜索数据。但没有数据显示。

MySql field name of "attributes_list" which is json type

MySql字段名称为“attributes_list”，它是json类型

My json data:

我的json数据：

{
 "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:

我对mysql的json查询：

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.

在产品表中有6行。在产品表中，有一个名称属性列表的字段。

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

该字段是json类型。保存产品的多个属性。我的attribute_list json格式是

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

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

现在我想搜索text ='Canada'，然后阅读那些产品信息

1 个解决方案

#1

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

根据mySql手册选择器$ **。文本将被使用。

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

[在页面上搜索（路径$ **。b）到达那里。]

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

此外，我试图执行上面提到的相同的任务

#1