Re: 多表联合查询，使用json_arrayagg会生成多条重复的数据

[bill lin]

请问如何能去除掉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账号，我自己的邮箱是[e-mail]，请联系我，谢谢