如何在MySQL中搜索JSON数据? [英] How to search JSON data in MySQL?

问题描述

我正在使用json_encoded将数据插入数据库中.现在，我要搜索功能"，但不能.

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

MySQL查询:

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

此查询向我显示所有键为"1"且值是任何东西而不是"3"的行

This query shows me all rows with key "1" and value is any thing not value is "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/zh-CN/json-search-functions.html

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

这篇关于如何在MySQL中搜索JSON数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！