如何在 MySQL 中编写可以解析列中的 JSON 数据的查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29137915/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can write queries in MySQL that can parse JSON data in a column?
提问by Chris Cinelli
I have a table in MySQL that has a column that store JSON objects. How can I easily run queries that can have some of the JSON fields in the WHERE clause?
我在 MySQL 中有一个表，它有一列存储 JSON 对象。如何轻松运行可以在 WHERE 子句中包含某些 JSON 字段的查询？
EX:
With a table named articles
EX：有一个名为的表 articles
+----+---------+--------------------------------------------------------------------------------------------------+
| id | user_id | json_data |
+----+---------+--------------------------------------------------------------------------------------------------+
| 1 | 1 | {"url":"https://www.cpubenchmark.net/","title": "CPU Benchmarks"} |
| 2 | 1 | {"url":"http://www.ebay.com/sch/CPUs-Processors-/164/i.html","title": "Computer and Processors"} |
| 3 | 2 | {"url":"https://www.youtube.com/watch?v=tntOCGkgt98","title": "Funny Cats Compilation" |
+----+---------+--------------------------------------------------------------------------------------------------+
I want to be able to write something like:
我希望能够写出类似的东西：
SELECT user_id, json_data FROM articles WHERE json_data.title LIKE "%CPU%"
That should return only the first row.
那应该只返回第一行。
回答by northtree
You could use json_extract(5.7 up).
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract
你可以使用json_extract（5.7 up）。
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract
SELECT user_id, json_data
FROM articles
WHERE json_extract(json_data, '$.title') LIKE '%CPU%';
回答by Chris Cinelli
I ended up solving in this way: https://github.com/ChrisCinelli/mysql_jsonwith a UDF. I detailed how to compile it and install it in the README. It works for me on Ubuntu 12.04.5on gcc version 4.6.3with MySQL 5.5
我最终以这种方式解决：https: //github.com/ChrisCinelli/mysql_json使用 UDF。我在自述文件中详细介绍了如何编译和安装它。它为我在Ubuntu 12.04.5对gcc version 4.6.3与MySQL 5.5
You will be able to run:
您将能够运行：
SELECT json_get('{"a":1}', 'a') => 1
SELECT json_get('{"a":1}', 'b') => NULL
SELECT json_get('[1,2,3]', 2) => 3
SELECT json_get('{"a":[2]}', 'a', 0) => 2
#Also:
SELECT json_get('{"a":{"b":2}}', 'a') => object
SELECT json_get('{"a":[1,2,3]}', 'a') => array
# Verify if it is a valid JSON:
SELECT ISNULL(json_get('{"a":1}')); => 0 # Valid
SELECT ISNULL(json_get('{"a":1')); => 1 # Invalid
# Create an example table:
CREATE TABLE `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` text,
PRIMARY KEY (`id`)
);
INSERT INTO message (id,data) VALUES(1,'{"from":"chris","title":"Awesome Article","body":"Lorem ipsum dolor sit amet, consectetur adipiscing elit."}');
INSERT INTO message (id,data) VALUES(2,'{"from":"loren","title":"Another Article","body":"Lorem ipsum dolor sit amet, consectetur adipiscing elit."}');
INSERT INTO message (id,data) VALUES(3,'{"from":"jason","title":"How to run a query","body":"Lorem ipsum dolor sit amet, consectetur adipiscing elit."}');
# Run queries on JSON values:
SELECT json_get(data,'title') FROM message WHERE id=2;
SELECT id,data FROM message WHERE json_get(data,'from')='chris';
SELECT id,data FROM message WHERE json_get(data,'title') LIKE '%Article%';
回答by Tim Biegeleisen
Try the following query and see if it fits your needs:
尝试以下查询，看看它是否符合您的需求：
SELECT user_id, json_data
FROM articles
WHERE common_schema.extract_json_value(json_data,'title')
LIKE "%CPU%"
This will only work on MySQLversion 5.1 or newer.
这仅适用于MySQL5.1 或更高版本。
