Menu
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":"\u0633\u0627\u062a\u0646"}, "6":{"value":""}, "7":{"value":""}, "8":{"value":""} }, "show_counter":"0", "show_counter_discount":"" }} 
351
Key takeaway for extracting data from a JSON field in MySQL: Use $. key to extract the value of a key from a JSON object. Use $[index] to extract the value of an element from a JSON array.
MySQL supports the -> operator as shorthand for this function as used with 2 arguments where the left hand side is a JSON column identifier (not an expression) and the right hand side is the JSON path to be matched within the column.
To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).
In MySQL, the only way to index a JSON path expression is to add a virtual column that mirrors the path expression in question and build an index on the virtual column. As you can see, the title column is mapped to the $. title path expression on the properties JSON column.
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
114
If your are using MySQL Latest version following may help to reach your requirement.
select * from products where attribs_json->"$.feature.value[*]" in (1,3) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
