我有以下架构+数据:
create table org_users ( id character varying (255), settings_id character varying (255) -- fk: settings.id ); create table settings ( id character varying (255), -- primary key settings_id perdiem_settings character varying (255), -- jsonised fk to perdiems.id floor_settings character varying (255) -- jsonised fk to floors.id ); create table perdiems ( id integer, -- primary key name character varying(255) ); create table floors ( id integer, -- primary key name character varying (255) ); insert into perdiems (id, name) values (1, 'perdiem 1'); insert into perdiems (id, name) values (2, 'perdiem 2'); insert into perdiems (id, name) values (3, 'perdiem 3'); insert into floors (id, name) values (1, 'floor 1'); insert into floors (id, name) values (2, 'floor 2'); insert into floors (id, name) values (3, 'floor 3'); insert into settings (id, perdiem_settings, floor_settings) values ('setting1', '{"allowed_per_diem_ids":[1, 2]}', '{"allowed_floor_ids":[1]}'); insert into settings (id, perdiem_settings, floor_settings) values ('setting2', '{"allowed_per_diem_ids":[2, 3]}', '{"allowed_floor_ids":[1, 2]}'); insert into settings (id, perdiem_settings, floor_settings) values ('setting3', '{"allowed_per_diem_ids":[3, 1]}', '{"allowed_floor_ids":[1, 2, 3]}'); insert into org_users (id, settings_id) values ('user1', 'setting1'); insert into org_users (id, settings_id) values ('user2', 'setting2'); insert into org_users (id, settings_id) values ('user3', 'setting3');
现在,我想创建一个视图,该视图将每个其他表的聚合汇总到自己的数组字段中。为了举例说明,我想要的视图应该像这样:
org_user_id | settings_id | perdiems | floors -------------------------------------------------------------------------------------------- user1 | setting1 | ['perdiem 1', 'perdiem 2'] | ['floor 1'] user2 | setting2 | ['perdiem 2', 'perdiem 3'] | ['floor 1', 'floor 2'] user3 | setting3 | ['perdiem 3', 'perdiem 1'] | ['floor 1', 'floor 2', 'floor 3']
这个问题在某种程度上与postgres聚合联接匹配到一个数组字段有关,该数组字段处理从联接匹配中创建数组字段的问题。但是,这里我想在单个视图中创建多个数组字段,因此使用GROUP BY子句在iiuc中将不可行。
GROUP BY
我尝试过的查询是:
CREATE OR REPLACE VIEW users_settings_view AS SELECT ou.id AS org_user_id, <other fields...> FROM org_users ou LEFT JOIN settings pdr_s ON pdr_s.id = ou.settings_id LEFT JOIN perdiems pdr ON pdr.id = ANY (SELECT json_array_elements(perdiem_settings::JSON->'allowed_per_diem_ids')::text::int FROM settings)
由于存在联接,因此会为每个匹配的永久对象创建重复记录,而不是创建数组。即使我像另一个stackoverflow问题中提到的那样创建了一个数组,但是如果我有多个字符串数组作为不同列的视图的一部分,它也将不起作用。我可以在单个视图中以多种方式将多个联接匹配到多个数组字段吗?
这将为您提供结果。
select ou.id, array_agg( DISTINCT pd.name ), array_agg( DISTINCT f.name ) from org_users ou join settings s on ou.settings_id = s.id cross join lateral json_array_elements_text(((s.perdiem_settings)::json->'allowed_per_diem_ids')::json) as jp(perdiem) join perdiems pd on pd.id = jp.perdiem::int cross join lateral json_array_elements_text(((s.floor_settings)::json->'allowed_floor_ids')::json) as js(floor) join floors f on f.id = js.floor::int GROUP BY ou.id;
演示版
编辑
对于NULL设置的情况,您可以单独使用UNION ALL
NULL
UNION ALL
select id , ARRAY[NULL] as perdiems ,ARRAY[NULL] as floors FROM org_users WHERE settings_id IS NULL UNION ALL ( -- The above query -- ) ORDER BY id;
演示2