postgres aggregate join matches to an array field

Question

I have the following schema + data:

create table org_users (
  id character varying (255),
  perdiem_ids character varying (255) --JSONized fk to perdiems.id. example data below
);

create table perdiems (
  id integer,
  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 org_users (id, perdiem_ids) values ('user1', '{"allowed_per_diem_ids":[1, 2]}');
insert into org_users (id, perdiem_ids) values ('user2', '{"allowed_per_diem_ids":[2, 3]}');
insert into org_users (id, perdiem_ids) values ('user3', '{"allowed_per_diem_ids":[3, 1]}');

Now, I want the list of allowed perdiem names for each org_user, for example, something like:

org_user_id | allowed_per_diem_names
------------|---------------------------
user1       | ['perdiem 1', 'perdiem 2']
user2       | ['perdiem 2', 'perdiem 3']
user3       | ['perdiem 3', 'perdiem 1']

If I use the following query, I can get the individual user's perdiem names, however the records are duplicated as it is a join.

select ou.id, p.name from org_users ou
    left join perdiems p ON p.id = ANY (SELECT json_array_elements(perdiem_ids::JSON->'allowed_per_diem_ids')::text::int from org_users);

outputs:

| id    | name      |
| ----- | --------- |
| user1 | perdiem 1 |
| user1 | perdiem 2 |
| user1 | perdiem 3 |
| user2 | perdiem 1 |
| user2 | perdiem 2 |
| user2 | perdiem 3 |
| user3 | perdiem 1 |
| user3 | perdiem 2 |
| user3 | perdiem 3 |

Now, what is the way to get the records in the formats that I expect ? I want the output of the join operations to become individual elements of an array field.

Kaushik Nayak · Accepted Answer · 2018-11-14 14:43:39Z

1

Use array_agg

select ou.id, array_agg(  pd.name  ORDER BY jp.id)
from org_users ou cross join lateral
    json_array_elements_text(((ou.perdiem_ids)::json->'allowed_per_diem_ids')::json)
        with ORDINALITY as jp(perdiem,id) join
      perdiems pd
   on pd.id = jp.perdiem::int
   GROUP BY ou.id;

Demo

answered Nov 14, 2018 at 14:43

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

4 Comments

Sankar Over a year ago

I have accepted the answer since it solves the problem, but is it possible to solve this without using the GROUP BY clause on ou.id ? Because, I am building a view on top of org_users which has some other fields and hence would require some other joins too (like the allowed_per_diem_ids, I may have fields like, allowed_cost_center_names, allowed_floor_names, etc.) on tables similar to the perdiems (cost_centers, floors, etc.).

Sankar Over a year ago

Basically I want to build a view like: org_user_id (text), perdiem_names (text[]), cost_center_names (text[]), floor_names (text[]) where the individual ids are stored in the org_users table. I just made the question here like this for brevity.

Kaushik Nayak Over a year ago

@Sankar : You may ask that as a separate question. I believe this answers your original question.

Sankar Over a year ago

Done. Asked it as a separate question stackoverflow.com/questions/53305373/… with some more details and a bit changed schema structure to explain the problem even more precisely. Thank you.

Gordon Linoff · Accepted Answer · 2018-11-14 11:44:20Z

0

I think this will get you there:

select *
from org_users ou cross join lateral
     json_array_elements_text(((ou.perdiem_ids)::json->'allowed_per_diem_ids')::json) ou_id join
     perdiems pd
     on pd.id = ou_id::int;

Here is a db<>fiddle.

answered Nov 14, 2018 at 11:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Sankar Over a year ago

This still returns duplicate records. I want only one row for each org_user.

Collectives™ on Stack Overflow

postgres aggregate join matches to an array field

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related