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
0
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
0
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
此外,我试图执行上面提到的相同的任务