如何在 MySQL 中搜索 JSON 数据?

2021-11-20 00:00:00 json mysql mysql-json

我在 mysql 数据库中插入了记录，数据类型为 json 编码，现在我必须在 json 编码 数据中进行搜索，但我无法得到正确的数据使用以下 MySql 查询.

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"$]'

查询结果是键等于1"；值是除了3"之外的任何东西

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

我的数据是:

{"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":""
}}

推荐答案

如果你有MySQL版本>= 5.7，那么你可以试试这个:

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

输出:

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


请查看 MySQL 参考手册以获取更多详细信息:
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

相关文章