MySQL Forums
Forum List  »  Chinese

Re: 多表联合查询,使用json_arrayagg会生成多条重复的数据
Posted by: bill lin
Date: January 20, 2020 08:33PM

请问如何能去除掉json_arrayagg中重复的数据

一、下面是查询的sql:

SELECT
JSON_PRETTY(
JSON_OBJECT(
'username',
a.username,
'password',
a.PASSWORD,
'payPassword',
a.pay_password,
'nameCn',
a.name_cn,
'roles',

JSON_ARRAYAGG(
JSON_OBJECT(
'roleCode',
c.role_code,
'roleName',
c.role_name
)),

'menus',
JSON_ARRAYAGG(
JSON_OBJECT(
'parentId',
e.parent_id,
'menuName',
e.menu_name,
'path',
e.path,
'menuLevel',
e.menu_level
)),
'privs',
JSON_ARRAYAGG(
JSON_OBJECT(
'menuId',
g.menu_id,
'privCode',
g.priv_code,
'privName',
g.priv_name
))
)

)
FROM
t_user a,
user_role b,
base_role c,
role_menu d,
base_menu e,
menu_priv f,
base_priv g
WHERE
a.id = 1
AND a.id = b.user_id
AND c.id = d.role_id
AND d.menu_id = e.id
and f.user_id=a.id
and f.menu_id=e.id
and f.priv_id=g.id;

二、下面是返回值
{
"menus": [
{
"path": "/index.html",
"menuName": "首页",
"parentId": null,
"menuLevel": 1
},
{
"path": "/index.html",
"menuName": "首页",
"parentId": null,
"menuLevel": 1
},
{
"path": "/left.html",
"menuName": "左边框",
"parentId": 1,
"menuLevel": 2
},
{
"path": "/left.html",
"menuName": "左边框",
"parentId": 1,
"menuLevel": 2
},
{
"path": "/right.html",
"menuName": "右边框",
"parentId": 1,
"menuLevel": 3
},
{
"path": "/right.html",
"menuName": "右边框",
"parentId": 1,
"menuLevel": 3
}
],
"privs": [
{
"menuId": 2,
"privCode": "indexDel",
"privName": "首页删除按钮"
},
{
"menuId": 2,
"privCode": "indexSubmit",
"privName": "首页提交按钮"
},
{
"menuId": 3,
"privCode": "leftDel",
"privName": "左边框删除按钮"
},
{
"menuId": 3,
"privCode": "leftSubmit",
"privName": "左边框提交按钮"
},
{
"menuId": 4,
"privCode": "rightQuery",
"privName": "右边框查询按钮"
},
{
"menuId": 4,
"privCode": "rightUpdate",
"privName": "右边框更新按钮"
}
],
"roles": [
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
},
{
"roleCode": "admin",
"roleName": "管理员"
}
],
"nameCn": "张闯",
"password": "112409",
"username": "zhangchuang",
"payPassword": "990522"
}

问题就在这儿,roles和menus中的值重复,因为是关联查询,所以导致生成了多条记录。

三、下面是原始数据

(1)去掉json的处理改为*

SELECT
*
FROM
t_user a,
user_role b,
base_role c,
role_menu d,
base_menu e,
menu_priv f,
base_priv g
WHERE
a.id = 1
AND a.id = b.user_id
AND c.id = d.role_id
AND d.menu_id = e.id
AND f.user_id = a.id
AND f.menu_id = e.id
AND f.priv_id = g.id;

(2)下面是查询到的值

id username password pay_password name_cn name_cn_pinyin name_en tel phone position job email state remark id(1) user_id role_id id(2) role_code role_name remark(1) id(3) role_id(1) menu_id id(4) parent_id menu_name path menu_level state(1) id(5) user_id(1) menu_id(1) priv_id id(6) menu_id(2) priv_code priv_name
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 1 1 2 2 首页 /index.html 1 1 2 1 2 2 2 2 indexDel 首页删除按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 1 1 2 2 首页 /index.html 1 1 1 1 2 1 1 2 indexSubmit 首页提交按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 2 1 3 3 1 左边框 /left.html 2 1 4 1 3 6 6 3 leftDel 左边框删除按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 2 1 3 3 1 左边框 /left.html 2 1 3 1 3 5 5 3 leftSubmit 左边框提交按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 3 1 4 4 1 右边框 /right.html 3 1 6 1 4 12 12 4 rightQuery 右边框查询按钮
1 zhangchuang 112409 990522 张闯 0 1 1 1 1 admin 管理员 最高级别 3 1 4 4 1 右边框 /right.html 3 1 5 1 4 11 11 4 rightUpdate 右边框更新按钮





三、这个账号是我再网上找的oracle账号,我自己的邮箱是724914749@qq.com,请联系我,谢谢

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 多表联合查询,使用json_arrayagg会生成多条重复的数据
698
January 20, 2020 08:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.