PostgreSQL基于数组类型的第二个查询来自第一个查询，即将查询与数组链接

Question

我有一个存储在数据库中的图形信息。每个节点都有一个整数 id 和文本标签，以及一个邻接列表，邻接列表是 id 的整数数组。在第一个查询中，我将获得一个节点列表，对于结果中的每个节点，我想获取与其相邻的所有节点的名称。

CREATE TABLE graph (id INTEGER, 
name TEXT,
adj_list INTEGER[],
PRIMARY KEY (id)
);

这是我想要实现的伪代码。

let node_list = (select * from graph where name like "X%");
foreach node in node_list:
foreach adj_node in node.adj_list:
print adj_node.name

任何人都可以建议我如何编写PostgreSQL查询来实现这一点吗？

下面是一些示例数据

id | name | adj 
---+------+------------
1 | X1   | {3, 4}
2 | X2   | {5, 6}
3 | Y1   | {..}
4 | Y2   | {..}
5 | Z1   | {..}
6 | Z2   | {..}

我想列出名称以X开头的节点的所有相邻节点。在此示例中，结果为 {Y1， Y2， Z1， Z2}。

Answer 1

如果您像@twelfth建议的那样构建另一个表，可能会容易得多。但是如果你确实想摇摆整数数组，我相信你可以做这样的事情：

--create the table
create table graph (
id integer,
name text,
adj_list integer[]
);
-- a sample insert
insert into graph (id, name, adj_list)
values
(1, 'X1', '{3,4}'),
(2, 'X2', '{5,6}'),
(3, 'Y1', '{}'),
(4, 'Y2', '{}'),
(5, 'Z1', '{}'),
(6, 'Z2', '{}')
;
-- use a CTE to unnest the array and give you a simple list of integers. 
-- In my opinion this CTE makes the code easier to read
with adjacent_ones as (
select unnest(adj_list) from graph where name like 'X%'
)
select * from graph where id in (select * from adjacent_ones);

这将为您提供以下内容

--------------------------
|id  |name   |adj_list   |
--------------------------
|3   |Y1     |{}         |
|4   |Y2     |{}         |
|5   |Z1     |{}         |
|6   |Z2     |{}         |
--------------------------