How to search JSON data in MySQL?

Question

I have inserted records in mysql DB, with json encoded data type, Now I have to make search within json encoded data, but i am not able to get proper data using following MySql query.

SELECT  `id` ,  `attribs_json` 
FROM  `products` 
WHERE  `attribs_json` REGEXP  '"1":{"value":[^"3"$]'

Query results are key equal to "1" and value is anything except "3"

My data is:

{"feature":{"1":{"value":"["2","3"]"},
            "2":{"value":["1"]},
            "5":{"value":""},
            "3":{"value":["1"]},
            "9":{"value":""},
            "4":{"value":"u0633u0627u062au0646"},
            "6":{"value":""},
            "7":{"value":""},
            "8":{"value":""}
           },
"show_counter":"0",
"show_counter_discount":""
}}

Answer 1

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"}  | 
+-------------------------------+

Please check MySQL reference manual for more details:
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html