场景:当一个列是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 来指示给定的 *
candidate
JSON 文档是否包含在
target
*JSON 文档中。所以这个只能查找一个完整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(*
column*
->*
path*)
*
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
版权归原作者 qq_26372385 所有, 如有侵权,请联系我们删除。