Mysql JSON对象和JSON数组查询

article/2025/11/7 4:38:36

文章目录

- - 1. 函数说明
  - 2. JSON对象
  - 3. 字符串JSON数组
  - - - 3.1 AND关系
      - 3.2 OR关系
  - 4. 对象数组
  - 5. 集成 Mybatis plus
  - 6. 模糊查询
  - 7. json_table
  - - - 7.1 分组计算总数
      - 7.2 对象去重

file_type可以是 varchar，也可以是 json类型

1. 函数说明

JSON_CONTAINS(json_doc, val[, path])：判断是否包含某个json值

JSON_ARRAY([val[, val] ...])：创建json数组

2. JSON对象

{"key": 1, "name": "万飞"}

查询

SELECT * FROM `ak_file_config` where file_type -> '$.name' = "万飞"

在这里插入图片描述

3. 字符串JSON数组

3.1 AND关系

["EXE", "白加黑", "DLL"]

查询

SELECT * FROM `ak_file_config` where JSON_CONTAINS(file_type, JSON_ARRAY("白加黑","DLL"))

在这里插入图片描述

3.2 OR关系

SELECT * FROM `ak_file_config` where JSON_CONTAINS(file_type,'"DLL"') OR JSON_CONTAINS(file_type,'"EXE"')

在这里插入图片描述

4. 对象数组

参考 https://wenku.baidu.com/view/0831b7cc6194dd88d0d233d4b14e852459fb3958?aggId=0831b7cc6194dd88d0d233d4b14e852459fb3958

5. 集成 Mybatis plus

// jsonArray查询
.apply(CollUtil.isNotEmpty(query.getFileType()), StrUtil.format("JSON_CONTAINS(t.file_type, JSON_ARRAY({}))",// 设置占位符{0},{1},{2}IntStream.range(0, Optional.ofNullable(query.getFileType()).orElse(Collections.emptyList()).size()).mapToObj(i -> "{".concat(String.valueOf(i)).concat("}")).collect(Collectors.joining(","))),Optional.ofNullable(query.getFileType()).orElse(Collections.emptyList()).toArray())

参考 https://blog.csdn.net/qq_31832209/article/details/125374325

6. 模糊查询

SELECT* 
FROM`ak_file_config` 
WHEREJSON_EXTRACT(file_type, '$' ) LIKE '%DL%';

在这里插入图片描述
代码里参考

String productOrCompanyName = query.getProductOrCompanyName().replace("\"", "_");.and(StrUtil.isNotBlank(productOrCompanyName),wq -> wq.apply("JSON_EXTRACT(LOWER(t.label_involve_product), '$') LIKE LOWER(CONCAT('%', {0}, '%'))", productOrCompanyName).or().apply("JSON_EXTRACT(LOWER(t.label_involve_company), '$') LIKE LOWER(CONCAT('%', {0}, '%'))", productOrCompanyName))

如果输入字符串带双引号，需要将\"替换成_进行模糊搜索，但是会查询出不带双引号的数据

7. json_table

mysql最低版本8.0.4

7.1 分组计算总数

SELECT* 
FROMJSON_TABLE ( '["11", "22"]', '$[*]' COLUMNS ( NESTED PATH '$' COLUMNS ( result INT PATH '$' ) ) ) AS t;

在这里插入图片描述
json对象数组参考 https://cdn.modb.pro/db/484630

在表中关联字符串数组分组查询
在这里插入图片描述
需求：统计各类型的数量

SELECTt1.result,count( t1.result ) AS count 
FROMpe_main_body tINNER JOIN JSON_TABLE ( t.overview_product_type, '$[*]' COLUMNS ( NESTED PATH '$' COLUMNS ( result VARCHAR ( 100 ) PATH '$' ) ) ) AS t1 
WHEREdel_flag = FALSE 
GROUP BYt1.result

在这里插入图片描述

7.2 对象去重

在这里插入图片描述
例如：match_context字段是对象数组，对象里面有两个字段keyWord和describe

需求：查询出所有的不重复的对象

SELECT DISTINCTt1.result 
FROMii_sensitive_resource_info tINNER JOIN JSON_TABLE ( t.match_context, '$[*]' COLUMNS ( NESTED PATH '$' COLUMNS ( result JSON PATH '$' ) ) ) AS t1 
WHEREcompany_id IN ( 296 )

DISTINCT：去重

在这里插入图片描述
改为字段返回

SELECTresult ->> '$.keyWord' AS keyWord,result ->> '$.describe' AS descInfo 
FROM(SELECT DISTINCTt1.result FROMii_sensitive_resource_info tINNER JOIN JSON_TABLE ( t.match_context, '$[*]' COLUMNS ( NESTED PATH '$' COLUMNS ( result JSON PATH '$' ) ) ) AS t1 
WHEREcompany_id IN ( 296 )) tmp

在这里插入图片描述