Mysql JSON对象和JSON数组查询

文章目录

• • • 1. 函数说明
    • 2. JSON对象
    • 3. 字符串JSON数组
    • • • 3.1 AND关系
        • 3.2 OR关系
    • 4. 对象数组
    • 5. 集成 Mybatis plus
    • 6. 模糊查询
    • 7. json_table
    • • • 7.1 分组计算总数
        • 7.2 对象去重
    • 8. JSONArray查询
    • • • 8.1 模糊查询
        • 8.2 等值匹配
        • 8.3 时间搜索
        • 8.4 在列表
        • 8.5 包含

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

->>：会去除双引号

8. JSONArray查询

• JSONArray 符号是$[*]，$[0]是数组对象时，不能查询所有，老版本不支持$[*]，使用新版本
• JSONObject 符号是 $

8.1 模糊查询

SELECT * FROM `tf_cloud`.`tf_low_data_testUser` WHERE  `address`->'$[0].name' LIKE "%b%"

8.2 等值匹配

SELECT * FROM `tf_cloud`.`tf_low_data_testUser` WHERE  `address`->'$[0].name' = "bbb"

8.3 时间搜索

SELECT * FROM `tf_cloud`.`tf_low_data_testUser` WHERE CAST(JSON_UNQUOTE(`address`->'$[0].date') AS DATETIME) BETWEEN '2023-08-13' AND '2023-08-17'

8.4 在列表

SELECT * FROM `tf_cloud`.`tf_low_data_testUser` WHERE  `address`->'$[0].name' IN ("bbb","ccc")

8.5 包含

SELECT * FROM `tf_cloud`.`tf_low_data_testUser` WHERE  json_contains(`address`->"$[*].nickname",'["bbb"]')

来源地址：https://blog.csdn.net/qq_38983728/article/details/126144608