Odtnhj

I have the following schema + data:

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

Now I want to create a view which will have aggregates from each of the other table, into an array field of its own. To explain with an example, the view that I want should be like:

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

This question is somewhat related to postgres aggregate join matches to an array field which deals with creating array fields out of join matches. However, here I want to create multiple array fields in a single view and so using a GROUP BY clause will not be feasible iiuc.

The query that I tried is:

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)

which creates duplicate records for each of the matching perdiem because of the join and not creating an array. Even if I crate an array as mentioned in the other stackoverflow question, it won't work if I have multiple string arrays as part of the view for different columns. Any way I can get multiple join matches to multiple array fields in a single view ?

This will give you the result.

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;

Demo

Edit

For cases of NULL settings, you may use a separate 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;

Demo2