postgres aggregate join matches to multiple array fields while creating views

Question

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 ?

Kaushik Nayak · Accepted Answer · 2018-11-15 16:54:59Z

3

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

edited Nov 15, 2018 at 16:54

answered Nov 15, 2018 at 8:05

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sankar Over a year ago

this works. However, this fails to execute when there are records with null values. A user could have null settings, A setting could be null for either perdiem or floor. For example: insert into org_users (id, settings_id) values ('user4', null) does not return the user4 in the final output.

Kaushik Nayak Over a year ago

@Sankar : you may use Left joins instead of inner join. I was just using your sample that's all.

Sankar Over a year ago

+Kaushik : Even if I change the from org_users ou join settings s on to from org_users ou left join settings s on I am not able to see the user4. dbfiddle.uk/… is the url. Or do I misunderstand which join you refer to ?

Kaushik Nayak Over a year ago

@Sankar : it may require a null check. I'm responding from my app now, will take a look and edit a while later. you may try with some options though

Kaushik Nayak Over a year ago

@Sankar : It seems a lot of changes would be required in the joins to handle the NULL case especially due to the fact that json also returns null. This may hamper the performance for large rows. So, I would suggest a simpler approach using UNION ALL See dbfiddle.uk/…

|

Collectives™ on Stack Overflow

postgres aggregate join matches to multiple array fields while creating views

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related