mysql json数据模糊查询

avatar
作者
筋斗云
阅读量:2

    场景:当一个列是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(字符串文字),以下三个表达式返回相同的值:

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

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!