How to search for JSON data in mysql?

advertisements

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

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