如何在MySQL中编写可解析列中JSON数据的查询? [英] How can write queries in MySQL that can parse JSON data in a column?

问题描述

我在MySQL中有一个表，该表具有存储JSON对象的列. 如何轻松运行在WHERE子句中可以包含某些JSON字段的查询?

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?

EX: 使用名为articles

EX: With a table named 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%"

那应该只返回第一行.

推荐答案

您可以使用json_extract(5.7或更高版本). https://dev.mysql.com /doc/refman/5.7/en/json-search-functions.html#function_json-extract

You could use 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%';

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