postgres聚合联接与数组字段匹配 [英] postgres aggregate join matches to an array field

问题描述

我有以下架构+数据:

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

现在，我想要每个org_user允许的永久名称的列表，例如:

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

输出:

| 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.

推荐答案

使用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;

演示

这篇关于postgres聚合联接与数组字段匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！