如何在 MySQL 中搜索 JSON 数据？

小开

在数据库中存储 JSON 违反了第一种常规形式。

您所能做的最好的事情就是将特性标准化并存储在另一个表中。然后，您将能够使用一个更好看的查询并使用联接执行查询。您的 JSON 甚至类似于表
Mysql 5.7具有内置的 JSON 功能:
Http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/

正确的模式是:

WHERE  `attribs_json` REGEXP '"1":{"value":[^}]*"3"[^}]*}'

[^}]将匹配除 }之外的任何字符

小开

我使用这个查询

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])key_word([^"])"';
or
SELECT id FROM table_name WHERE field_name RLIKE '"key_name":"[[:<:]]key_word[[:>:]]"';

第一个查询我使用它来搜索部分值。第二个查询我使用它来搜索确切的单词。

小开

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

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

小开

如果您正在使用 MySQLLatest 版本以下可能有助于达到您的要求。

select * from products where attribs_json->"$.feature.value[*]" in (1,3)

小开

用于 MySQL all (和5.7)

SELECT LOWER(TRIM(BOTH 0x22 FROM TRIM(BOTH 0x20 FROM SUBSTRING(SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"'),LOCATE(0x2C,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"')+1,LENGTH(json_filed)))),LOCATE(0x22,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"'),LOCATE(0x2C,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"')+1,LENGTH(json_filed))))),LENGTH(json_filed))))) AS result FROM `table`;

小开

我觉得..。

Search partial value:

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])*key_word([^"])*"';

搜索确切词汇:

SELECT id FROM table_name WHERE field_name RLIKE '"key_name":"[[:<:]]key_word[[:>:]]"';

小开

为了神秘8->

质疑:

SELECT properties, properties->"$.price" FROM book where isbn='978-9730228236' and  JSON_EXTRACT(properties, "$.price") > 400;

资料:

mysql> select * from book\G;
*************************** 1. row ***************************
id: 1
isbn: 978-9730228236
properties: {"price": 44.99, "title": "High-Performance Java Persistence", "author": "Vlad Mihalcea", "publisher": "Amazon"}
1 row in set (0.00 sec)

小开

如果 MySQL 版本 < 5.7

SELECT fields
FROM table
WHERE field_json LIKE '%"key":"70"%';

//70 = 价值

小开

请按我说的做。

SELECT * FROM `products`
WHERE JSON_UNQUOTE(JSON_EXTRACT(`attribs_json`, '$.value')) LIKE '%X%'

小开

SELECT
country.NAME AS 'country_name',
city.NAME AS 'city_name',
city.district,
city.info,
JSON_EXTRACT( city.info, "$.Population" ) AS 'formated_population'
FROM
city
INNER JOIN country ON city.CountryCode = country.
CODE INNER JOIN countrylanguage ON country.CODE = countrylanguage.CountryCode
GROUP BY
city.NAME,
city.district,
country.NAME;


ORDER BY
country.NAME ASC;