個(gè)人中心
退出
MySql中json類(lèi)型數(shù)據(jù)的查詢(xún)以及在MyBatis-Plus中的使用
表結(jié)構(gòu)和初始數(shù)據(jù)
新建表結(jié)構(gòu)
CREATE TABLE `json_test` (
`id` int NOT NULL AUTO_INCREMENT,
`roles` json DEFAULT NULL COMMENT '角色',
`project` json DEFAULT NULL COMMENT '項(xiàng)目',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
初始數(shù)據(jù)
INSERT INTO `ctts_dev`.`json_test`(`id`, `roles`, `project`) VALUES (1, '[{"id": 10001, "name": "管理員"}, {"id": 10002, "name": "開(kāi)發(fā)"}, {"id": 10003, "name": "測(cè)試"}]', '{"id": 11111, "name": "項(xiàng)目1"}');
INSERT INTO `ctts_dev`.`json_test`(`id`, `roles`, `project`) VALUES (2, '[{"id": 10002, "name": "開(kāi)發(fā)"}]', '{"id": 22222, "name": "項(xiàng)目2"}');
INSERT INTO `ctts_dev`.`json_test`(`id`, `roles`, `project`) VALUES (3, '[{"id": 10003, "name": "測(cè)試"}]', '{"id": 11111, "name": "項(xiàng)目1"}');
復(fù)制代碼
其中 roles 字段存的是 數(shù)組
[
{"id":10001,"name":"管理員"},
{"id":10002,"name":"開(kāi)發(fā)"},
{"id":10003,"name":"測(cè)試"}
]
project 存的是 對(duì)象
{"id": 11111, "name": "項(xiàng)目1"}
對(duì)象JSON查詢(xún)
查詢(xún) project 是 項(xiàng)目1 的（精準(zhǔn)查詢(xún)）
使用箭頭函數(shù)
SELECT * FROM json_test
WHERE project -> '$.name' = '項(xiàng)目1'
使用 JSON_CONTAINS
SELECT * FROM json_test
WHERE JSON_CONTAINS(project, JSON_OBJECT('name', '項(xiàng)目1'))
模糊查詢(xún) project 字段
SELECT * FROM json_test
WHERE project -> '$.name' like '%項(xiàng)目%'
數(shù)組JSON查詢(xún)
查詢(xún) roles 中包含 測(cè)試 的（精準(zhǔn)查詢(xún)）
SELECT * FROM json_test
WHERE JSON_CONTAINS(roles,JSON_OBJECT('name','測(cè)試'))
模糊查詢(xún) roles 字段
SELECT * FROM json_test
WHERE roles -> '$[*].name' like '%測(cè)%'
其中，中括號(hào)里的 星號(hào) ，也可以替換為數(shù)組中的下標(biāo)index
SELECT * FROM json_test
WHERE roles -> '$[1].name' like '%測(cè)%'
這樣就是模糊查詢(xún)?cè)跀?shù)組中第二個(gè)元素是否含有 測(cè) 字的，
提取JSON中數(shù)據(jù)
提取 json 中單個(gè)字段的值
SELECT
id,
roles -> '$[*].name' AS roleName,
project ->> '$.name' AS projectName
FROM
json_test
也提供了 JSON_EXTRACT 函數(shù)，結(jié)果和上面是一樣的，只是查詢(xún)出的 projectName 是帶雙引號(hào)的
SELECT
id,
JSON_EXTRACT( roles, '$[*].name' ) AS roleName,
JSON_EXTRACT( project, '$.name' ) AS projectName
FROM
json_test
如果不需要雙引號(hào)，可以使用 JSON_UNQUOTE 函數(shù)
提取 json 中所有字段的值，用 星號(hào) 就行
SELECT
id,
roles -> '$[*].*' AS roleName,
project ->> '$.*' AS projectName
FROM
json_test
一個(gè)箭頭和兩個(gè)箭頭的區(qū)別
MySql中箭頭函數(shù)，一個(gè)箭頭和兩個(gè)箭頭的區(qū)別
如果是字符串，一個(gè)箭頭返回的結(jié)果是帶雙引號(hào)的（只針對(duì)字符串），兩個(gè)箭頭是不帶的
SELECT project -> '$.name' as projectName FROM json_test;
"項(xiàng)目1"
"項(xiàng)目2"
"項(xiàng)目1"
SELECT project ->> '$.name' as projectName FROM json_test;
項(xiàng)目1
項(xiàng)目2
項(xiàng)目1
JSON_UNQUOTE 函數(shù)也是去掉雙引號(hào)，等價(jià)于使用兩個(gè)箭頭
SELECT project ->> '$.name' as projectName FROM json_test
等價(jià)于
SELECT JSON_UNQUOTE(project -> '$.name') as projectName FROM json_test
等價(jià)于
SELECT JSON_UNQUOTE(JSON_EXTRACT(project, '$.name')) as projectName FROM js
on_test
可以看出還是箭頭函數(shù)方便
復(fù)雜JSON
多層復(fù)雜的 json 結(jié)構(gòu)，提取想要的數(shù)據(jù)，也只需要按照key點(diǎn)下去即可
{
"status": 1,
"message": "SUCCESS",
"data": {
"list": [
{
"id": "1565510388254765086",
"name": "測(cè)試1111",
"object": [
{
"id": "1565510583118209025",
"tenderName": "客戶(hù)1"
}
]
},
{
"id": "1565504356392558665",
"name": "測(cè)試2222",
"object": [
{
"id": "1565504633934229506",
"tenderName": "客戶(hù)2"
}
]
}
]
}
}
SELECT other ->> '$.data.list[*].contractObject'
FROM json_test
-other 為表字段名
如果不知道 key，也可以用 星號(hào) 代替，但是也要知道是在第幾層
SELECT other ->> '$**.contractObject'
FROM json_test
在LambdaQueryChainWrapper中使用
在 MyBatis-Plus 中 使用 LambdaQueryChainWrapper 模糊查詢(xún) json 數(shù)據(jù)
new LambdaQueryChainWrapper<>(baseMapper)
.like(StringUtils.isNotBlank(req.getTitle()), Test::getTitle, req.getTitle())
.apply(StringUtils.isNotBlank(req.getRoles()), "roles ->> '$[*].name' LIKE CONCAT('%',{0},'%')", req.getRoles())
.apply(StringUtils.isNotBlank(req.getProject()), "project -> '$.name' LIKE CONCAT('%',{0},'%')", req.getProject())
.eq(req.getDeleted() != null, Test::getDeleted, req.getDeleted())
.orderByDesc(Test::getCreatedAt)
.list();
使用 LambdaQueryChainWrapper 準(zhǔn)確查詢(xún) json 數(shù)據(jù)
new LambdaQueryChainWrapper<>(baseMapper)
.apply(StringUtils.isNotBlank(req.getRoles()), "JSON_CONTAINS(roles, JSON_OBJECT('name',{0}))", req.getRoles())
.apply(StringUtils.isNotBlank(req.getProject()), "project -> '$.name' = {0}", req.getProject())
.orderByDesc(Test::getCreatedAt)
.list();
使用 LambdaQueryChainWrapper 的話 ，一般都是用 apply 拼接自定義 sql
apply 是可以通過(guò)占位符的形式，傳入多個(gè)參數(shù)的，也比較方便
LambdaQueryChainWrapper select 提取JSON數(shù)據(jù)
apply 是用做數(shù)據(jù)篩選用，那如果想 select 提取 json 中的數(shù)據(jù)呢
首先需要在對(duì)應(yīng)的實(shí)體類(lèi)里新增一個(gè)字段，比如 roleName （不需要在表中創(chuàng)建該字段）
@TableField(value = "roles ->> '$[*].name'",
insertStrategy = FieldStrategy.NEVER,
updateStrategy = FieldStrategy.NEVER,
select = false)
private String roleName;
其中，value 對(duì)應(yīng)的就是 select xxxx 形式
insertStrategy = FieldStrategy.NEVER 不執(zhí)行插入
updateStrategy = FieldStrategy.NEVER 不執(zhí)行更新
select = false 不讓該字段出現(xiàn)在 select 中
上述3個(gè)配置是為了不影響已有的增刪改查，因?yàn)橹皇窃趯?shí)體類(lèi)中新增了字段，沒(méi)有在對(duì)應(yīng)的表中增加該字段，可以根據(jù)自己的實(shí)際需求配置
然后就可以在 LambdaQueryChainWrapper 中使用了
new LambdaQueryChainWrapper<>(baseMapper)
.select(Test::getRoleName)
.list();
對(duì)應(yīng)執(zhí)行的 sql 如下
SELECT roles ->> '$[*].name' AS roleName FROM json_tes
這種方法可以在 select 中用 MySql 里的函數(shù)，比如常用的 SUM、MAX、COUNT 等
@TableField(value = "SUM(money)",
insertStrategy = FieldStrategy.NEVER,
updateStrategy = FieldStrategy.NEVER,
select = false)
private BigDecimal moneyCount;
哎，就是不想在xml中寫(xiě)sql...(小聲bb)
作者：碼出宇宙
歡迎關(guān)注微信公眾號(hào) ：碼出宇宙
掃描添加好友邀你進(jìn)技術(shù)交流群，加我時(shí)注明【姓名+公司(學(xué)校)+職位】
分類(lèi)導(dǎo)航