sql - 使用 Posgtres JSON 函数从与其他表有关系的表中生成完整的 JSON 对象
问题描述
我想使用 Postgres 从 'user'、'user_role'、'role' 和 'permission' 生成用户 JSON 对象,如下所示:
CREATE TABLE "user" (
id SERIAL PRIMARY KEY,
name TEXT,
location TEXT
);
CREATE TABLE "role" (
id INT NOT NULL PRIMARY KEY,
name TEXT
);
CREATE TABLE "user_role" (
id SERIAL PRIMARY KEY,
user_id BIGINT,
role_id INT,
FOREIGN KEY ("user_id") REFERENCES "user"("id"),
FOREIGN KEY ("role_id") REFERENCES "role"("id")
);
CREATE TABLE "permission" (
id INT NOT NULL PRIMARY KEY,
role_id INT,
name TEXT,
FOREIGN KEY ("role_id") REFERENCES "role"("id")
);
INSERT INTO "user" ("name", "location") VALUES ('Hamed', 'Berlin');
INSERT INTO "user" ("name", "location") VALUES ('Zhang', 'Shenzhen');
INSERT INTO "user" ("name", "location") VALUES ('Jake', 'Vancouver');
INSERT INTO "role" ("id", "name") VALUES (1, 'admin');
INSERT INTO "role" ("id", "name") VALUES (2, 'user');
INSERT INTO "permission" ("id", "role_id", "name") VALUES (1, 1, 'add');
INSERT INTO "permission" ("id", "role_id", "name") VALUES (2, 1, 'delete');
INSERT INTO "permission" ("id", "role_id", "name") VALUES (3, 1, 'update');
INSERT INTO "permission" ("id", "role_id", "name") VALUES (4, 2, 'read');
INSERT INTO "user_role" ("user_id", "role_id") VALUES (1, 1);
INSERT INTO "user_role" ("user_id", "role_id") VALUES (1, 2);
INSERT INTO "user_role" ("user_id", "role_id") VALUES (2, 1);
SELECT (
json_build_object (
'id', u.id,
'roles', json_agg(
r.id
)
)
) FROM "user" AS u
LEFT JOIN "user_role" AS r ON r.user_id = u.id
GROUP by u.id;
SELECT (
json_build_object (
'id', r.id,
'permissions', json_agg(
p.id
)
)
) FROM "role" AS r
LEFT JOIN "permission" AS p ON p.role_id = r.id
GROUP by r.id;
输出是:
--------------------------------
1 | {"id" : 1, "roles" : [1, 2]}
2 | {"id" : 2, "roles" : [3]}
3 | {"id" : 3, "roles" : [null]}
--------------------------------
1 | {"id" : 1, "permissions" : [1, 2, 3]}
2 | {"id" : 2, "permissions" : [4]}
我有两个问题:
- 如何摆脱[null]。我们可以用[] of 代替吗?
- 我想生成这个结果:
--------------------------------
1 | {"id" : 1, "roles" : [1, 2], "permissions": [1, 2, 3, 4]}
2 | {"id" : 2, "roles" : [3] , "permissions": [4]}
3 | {"id" : 3, "roles" : [null], "permissions": []}
查询应该是什么样子?您也可以在 DB fiddle中找到代码并在此处运行它。谢谢。
解决方案
role
首先,您应该从表中获取用户的角色,user_role
只是一个桥接表。查看查询结果:
select
u.id as user_id,
ur.id as user_role_id,
r.id as role_id,
p.id as permission_id
from "user" u
left join "user_role" ur on ur.user_id = u.id
left join "role" r on r.id = ur.role_id
left join "permission" p on p.role_id = r.id
user_id | user_role_id | role_id | permission_id
---------+--------------+---------+---------------
1 | 1 | 1 | 3
1 | 1 | 1 | 2
1 | 1 | 1 | 1
1 | 2 | 2 | 4
2 | 3 | 1 | 3
2 | 3 | 1 | 2
2 | 3 | 1 | 1
3 | | |
(8 rows)
用户 #2 具有角色 #1(不是 #3)。
您可以轻松地转换查询以获取聚合数据:
select
jsonb_build_object(
'id', u.id,
'roles', jsonb_agg(distinct r.id order by r.id),
'permissions', jsonb_agg(p.id order by p.id))
from "user" u
left join "user_role" ur on ur.user_id = u.id
left join "role" r on r.id = ur.role_id
left join "permission" p on p.role_id = r.id
group by u.id
jsonb_build_object
---------------------------------------------------------
{"id": 1, "roles": [1, 2], "permissions": [1, 2, 3, 4]}
{"id": 2, "roles": [1], "permissions": [1, 2, 3]}
{"id": 3, "roles": [null], "permissions": [null]}
(3 rows)
没有标准函数可以去除 jsonb 数组的空元素。您可以创建自己的:
create or replace function jsonb_strip_null_elements(jsonb)
returns jsonb language sql immutable as $$
select coalesce(jsonb_agg(e), '[]')
from jsonb_array_elements($1) as e
where e <> 'null'
$$;
推荐阅读
- javascript - 如何从一个 html 发送“分数”。使用 localstorage 和 JSON 文件到另一个文件
- python - 如何对两个折线图之间的区域进行着色
- php - 如何在 url 路径而不是子域上托管 wordpress
- python - 无法从 Android 上的 Kivy App 发送蓝牙
- asp.net-core - 上传 Excel 文件导致错误连接休息
- javascript - 尝试使用 MERN 上传图像时出现 400 错误请求错误
- java - 无法检查 Java 调试器 VScode 中的值
- azure - 每个函数 hosts.json 设置而不是全局
- arrays - 角度模板数组插值
- git - 是否有 git 全局配置来显示克隆进度