Блог

Я хочу использовать Postgres для создания объекта user JSON из ‘user’, ‘user_role’, ‘role’ и ‘permission’ следующим образом:

 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]}
  

У меня есть две проблемы:

1. Как избавиться от [null]. Можем ли мы вместо этого использовать [] of?
2. Я хочу сгенерировать этот результат:

 --------------------------------
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'
$$;
  

Живая демонстрация в db-fiddle.

Если вы хотите вернуть роли и разрешения для каждого пользователя, вы можете сделать это:

 select
    to_json(a)
from (
    select
        u.id,
        coalesce(json_agg(distinct r.role_id) filter (where r.role_id is not null), '[]'::json) as roles,
        coalesce(json_agg(distinct p.id) filter (where p.id is not null), '[]'::json) as permissions
    from "user" as u
        left join "user_role" as r on
            r.user_id = u.id
        left join "permission" as p on
            p.role_id = r.role_id
    group by
        u.id
) as a
  

db-fiddle demo