0


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 来指示给定的 *
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

标签: mysql json

本文转载自: https://blog.csdn.net/qq_26372385/article/details/135977343
版权归原作者 qq_26372385 所有, 如有侵权,请联系我们删除。

“mysql json数据模糊查询”的评论:

还没有评论