postgresql JAVA 数组_postgresql：与数组的自联接

我的问题是关于为以下用例形成Postgres SQL查询

Approach#1

我有一个像下面的表，我在不同的类型(a，b，c，d)生成相同的uuid，如映射不同的类型 .

+----+------+-------------+

| id | type | master_guid |

+----+------+-------------+

| 1 | a | uuid-1 |

| 2 | a | uuid-2 |

| 3 | a | uuid-3 |

| 4 | a | uuid-4 |

| 5 | a | uuid-5 |

| 6 | b | uuid-1 |

| 7 | b | uuid-2 |

| 8 | b | uuid-3 |

| 9 | b | uuid-6 |

| 10 | c | uuid-1 |

| 11 | c | uuid-2 |

| 12 | c | uuid-3 |

| 13 | c | uuid-6 |

| 14 | c | uuid-7 |

| 15 | d | uuid-6 |

| 16 | d | uuid-2 |

+----+------+-------------+

Approach#2

我有一个创建的两个表用于id键入，然后id到master_guid，如下所示

table1:

+----+------+

| id | type |

+----+------+

| 1 | a |

| 2 | a |

| 3 | a |

| 4 | a |

| 5 | a |

| 6 | b |

| 7 | b |

| 8 | b |

| 9 | b |

| 10 | c |

| 11 | c |

| 12 | c |

| 13 | c |

| 14 | c |

| 15 | d |

| 16 | d |

+----+------+

table2

+----+-------------+

| id | master_guid |

+----+-------------+

| 1 | uuid-1 |

| 2 | uuid-2 |

| 3 | uuid-3 |

| 4 | uuid-4 |

| 5 | uuid-5 |

| 6 | uuid-1 |

| 7 | uuid-2 |

| 8 | uuid-3 |

| 9 | uuid-6 |

| 10 | uuid-1 |

| 11 | uuid-2 |

| 12 | uuid-3 |

| 13 | uuid-6 |

| 14 | uuid-7 |

| 15 | uuid-6 |

| 16 | uuid-2 |

+----+-------------+

我希望通过以下两种方法获得如下输出：

+----+------+--------+------------+

| id | type | uuid | mapped_ids |

+----+------+--------+------------+

| 1 | a | uuid-1 | [6,10] |

| 2 | a | uuid-2 | [7,11] |

| 3 | a | uuid-3 | [8,12] |

| 4 | a | uuid-4 | null |

| 5 | a | uuid-5 | null |

+----+------+--------+------------+

我已尝试使用array上的array_agg进行自联接，并基于uuid进行分组，但无法获得所需的输出 .

使用以下查询来填充数据：

Approach#1

insert into table1 values

(1,'a','uuid-1'),

(2,'a','uuid-2'),

(3,'a','uuid-3'),

(4,'a','uuid-4'),

(5,'a','uuid-5'),

(6,'b','uuid-1'),

(7,'b','uuid-2'),

(8,'b','uuid-3'),

(9,'b','uuid-6'),

(10,'c','uuid-1'),

(11,'c','uuid-2'),

(12,'c','uuid-3'),

(13,'c','uuid-6'),

(14,'c','uuid-7'),

(15,'d','uuid-6'),

(16,'d','uuid-2')

Approach#2

insert into table1 values

(1,'a'),

(2,'a'),

(3,'a'),

(4,'a'),

(5,'a'),

(6,'b'),

(7,'b'),

(8,'b'),

(9,'b'),

(10,'c'),

(11,'c'),

(12,'c'),

(13,'c'),

(14,'c'),

(15,'d'),

(16,'d')

insert into table2 values

(1,'uuid-1'),

(2,'uuid-2'),

(3,'uuid-3'),

(4,'uuid-4'),

(5,'uuid-5'),

(6,'uuid-1'),

(7,'uuid-2'),

(8,'uuid-3'),

(9,'uuid-6'),

(10,'uuid-1'),

(11,'uuid-2'),

(12,'uuid-3'),

(13,'uuid-6'),

(14,'uuid-7'),

(15,'uuid-6'),

(16,'uuid-2')