mysql查找json数据库_如何在MySQL中搜索JSON数据？

I am inserting my data in a database with json_encoded. Now I want to search in "feature", but I can't.

MySQL query:

SELECT `id` , `attribs_json`

FROM `products`

WHERE `attribs_json` REGEXP '"1":{"value":[^"3"$]'

This query shows me all rows with key "1" and value is any thing not value is "3"

My data is:

{"feature":{"1":{"value":"["2","3"]"},

"2":{"value":["1"]},

"5":{"value":""},

"3":{"value":["1"]},

"9":{"value":""},

"4":{"value":"\u0633\u0627\u062a\u0646"},

"6":{"value":""},

"7":{"value":""},

"8":{"value":""}

},

"show_counter":"0",

"show_counter_discount":""

}}

解决方案

If you have MySQL version >= 5.7, then you can try this:

SELECT JSON_EXTRACT(name, "$.id") AS name

FROM table

WHERE JSON_EXTRACT(name, "$.id") > 3

Output:

+-------------------------------+

| name |

+-------------------------------+

| {"id": "4", "name": "Betty"} |

+-------------------------------+