mysql json数据模糊查询
场景：当一个列是json字符串时，想模糊查询json字符串中某个字段。
mysql5.7版本已经有了json数据类型，并且有了处理json数据类型的函数。具体看下面的文档地址。这里只说当需要对json中某个字段模糊查询时该怎么写。可以直接用 like的模糊查询和re来查，但是效果很不好，所以直接推荐使用JSON_SEARCH。
说下几个函数，JSON_SEARCH,JSON_CONTAIN,JSON_EXTRACT。因为JSON_SEARCH直接返回的就是查询的结果，所以这里主要讲的是JSON_SEARCH，可以放在select中也可以放在where中，放在where查询条件中就是当做模糊搜索了。
1、JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
各个参数：
json_doc：待查询的json字符串，可以是字符串也可以是列名。放json字符串
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'one', 'abc');放json的列名，假如有表 user, id,name,age,address,email。其中address是json类型（text类型，但是存的是 json字符串也行），那么也可以这样查询
SELECT JSON_SEARCH(address, 'one', 'abc');one_or_all：返回第一个结果还是返回全部结果，只有两个值，one搜索到第一个结果后返回路径字符串。all返回所有搜索到的路径字符串，组装为数组。
search_str：查询的字符串，关键就是这里，可以使用 %，_，也就是和like一样。如
SELECT JSON_SEARCH(address, 'all', '%a%');但是也要注意转义字符的处理。
escape_char：转义字符，如果缺失，默认是\，否则需要写NULL,或者单个字符来指定。一般给NULL就可以。
path：路径表达式（json对象的字段），这里也是关键的地方，如果是想在复杂的json中模糊查询数据，这个是最关键的。
demo：
假如有张表，user,列id int,name varchar,age int,address json。
address数据如下
[
{
"province": "jiangsu",
"city": "nanjing",
"district": "jiangning"
},
{
"province": "jiangsu",
"city": "suzhou",
"district": "wuzhong"
}
]
查询所有city中有i的
select JSON_SEARCH('[ { "province": "jiangsu", "city": "nanjing", "district": "jiangning" }, { "province": "jiangsu", "city": "suzhou", "district": "wuzhong" }, { "province": "jiangsu", "city": "wuxi", "district": "binhu" } ]', 'all', '%i%', null,'$[*].city');
查询所有city中 有字母 p的：
select JSON_SEARCH('[ { "province": "jiangsu", "city": "nanjing", "district": "jiangning" }, { "province": "jiangsu", "city": "suzhou", "district": "wuzhong" }, { "province": "jiangsu", "city": "wuxi", "district": "binhu" } ]', 'all', '%p%', null,'$[*].city');结果没有匹配的数据
所以如果想找到user表中，address列里所有城市有nan的可以这么查
select * from user where JSON_SEARCH('address', 'all', '%nan%', null,'$[*].city');获取对象和获取列表分别举例如下
对象：SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name')
数组：SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]'); 提取字段c数组的所有元素。
通配符*很有用，$.*来匹配对象的所有字段，$.c[*]来匹配数组的所有元素。
需要注意的地方：
特殊字符的转义，需要对 /斜杠，\反斜杠，%通配符，_占位符自己处理转义。如
"_", "\_" ;"%", "\%";"/", "\\\\/"; "\\", "\\\\\\\\" 注：这个对反斜杠的处理需要根据具体项目来修改
2、JSON_CONTAINS(target, candidate[, path])
通过返回 1 或 0 来指示给定的 candidateJSON 文档是否包含在targetJSON 文档中。所以这个只能查找一个完整json数据，想模糊查询子字符串不行。
查询一个完整的数据
模糊查询子字符串，失败。select JSON_CONTAINS('{"a": 1, "b": "66778899", "c": {"d": 4}}', '6', '$.b');
3、JSON_EXTRACT(json_doc, path[, path] ...)
从 JSON 文档返回数据，该数据是从与参数匹配的文档部分中选择的path
mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); +----------------------------------------------------+ | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') | +----------------------------------------------------+ | [20, 10] | +----------------------------------------------------+这里返回的是从json字符串中提取选定的路径中的数据。也不符合模糊查询。但是加上运算符就可以。这里还可以用表达式
mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY JSON_EXTRACT(c, "$.name"); +-------------------------------+-----------+------+ | c | c->"$.id" | g | +-------------------------------+-----------+------+ | {"id": "3", "name": "Barney"} | "3" | 3 | | {"id": "4", "name": "Betty"} | "4" | 4 | | {"id": "2", "name": "Wilma"} | "2" | 2 | +-------------------------------+-----------+------+直接看文档：
MySQL :: MySQL 5.7 Reference Manual :: 12.17.3 Functions That Search JSON Values
MySQL :: MySQL 5.7 Reference Manual :: 12.17.3 Functions That Search JSON Values
JSON_EXTRACT(c, "$.id")
c->"$.id"
特别是加上JSON_UNQUOTE()后
这是 MySQL 5.7.13 及更高版本中提供的改进的不带引号的提取运算符。虽然该 ->运算符只是提取一个值，但该 ->>运算符还取消引用提取的结果。换句话说，给定一个 JSON列值 column和一个路径表达式 path（字符串文字），以下三个表达式返回相同的值：
JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(->columnpath)column->>path
mysql> SELECT c->'$.name' AS name -> FROM jemp WHERE g > 2; +----------+ | name | +----------+ | "Barney" | | "Betty" | +----------+ 2 rows in set (0.00 sec) mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name -> FROM jemp WHERE g > 2; +--------+ | name | +--------+ | Barney | | Betty | +--------+ 2 rows in set (0.00 sec) mysql> SELECT c->>'$.name' AS name -> FROM jemp WHERE g > 2; +--------+ | name | +--------+ | Barney | | Betty | +--------+这样就完全可以用来查询了，因为去掉了转义，去掉[]，就只剩下数据了，可以用 =, , >=, <>, !=, and 来进行比较过滤，筛选了。而且把提取运算符的结果作为模糊查询的条件也能实现模糊查询，如 （这个只是例子，大概的思路），要注意这个要特定版本。
JSON_UNQUOTE(c->'$.name') LIKE '%p%'""
最后总结下：
1.查询字符串，可以使用通配符%,_占位符像LIKE一样但是需要注意转义字符。
2.路径表达式，可以搜索匹配复杂结构的json字符串中的字段。
参考文档：
MySQL :: MySQL 5.7 Reference Manual :: 11.5 The JSON Data Type
MySQL :: MySQL 5.7 Reference Manual :: 12.17.3 Functions That Search JSON Values
MySQL :: MySQL 5.7 Reference Manual :: 12.8.1 String Comparison Functions and Operators