在PostgreSQL中查找指定节点集合内的所有关联边

表结构 #

objects表 #

object_relationships表 #

初始节点查询 #

执行以下查询得到目标节点集合[1,2,3,5]：

select * from objects where description like '%birds%' or description like '%lex%' or description like '%john%'

目标需求 #

需要获取上述节点之间的所有关联边，预期结果：

• 1 - to - 3
• 3 - from - 1
• 2 - from - 5
• 5 - friends - 3

现有问题代码 #

当前使用的JOIN方式会引入外部节点，且无法正确筛选目标边，代码如下：

json_build_object(
    'source', base.object_id,
    'target', base.child_id,
    'type', base.child_type
) as edge1,
json_build_object(
    'source', base.child_id,
    'target', base.child2_id,
    'type', base.child2_type
) as edge2 from
(
with parent as (
select
    distinct unnest(array[base.object_id, base.child_id, base.child2_id]) as id
from
    (
    select 
                o.id as object_id,
                o.type,
                or1.child_object_id as child_id,
                or1."type" as child_type
                or2.child_object_id as child2_id,
                or2."type" as child2_type
    from
        objects o
    join object_relationships or1 on
        or1.object_id = o.id
    join objects o1 on
        o1.id = or1.child_object_id
    join object_relationships or2 on or2.object_id = o1.id
    join objects o2 on o2.id = or2.child_object_id
    where
        o.description like '%birds%' or o.description like '%lex%' or o.description like '%john%'
    limit 1) base
limit 100)
select
    o.id as object_id,
    or1.child_object_id as child_id,
    or1."type" as child_type,
    or2.child_object_id as child2_id,
    or2."type" as child2_type
from
    parent p
join objects o on
    o.id = p.id
join object_relationships or1 on
    or1.object_id = o.id
join objects o1 on
    o1.id = or1.child_object_id
join object_relationships or2 on
    or2.object_id = o1.id
join objects o2 on
    o2.id = or2.child_object_id
limit 100) base;

正确查询方法 #

核心思路是先锁定目标节点集合，再筛选关联表中两端节点都属于该集合的边，以下提供两种输出格式的查询：

方法一：输出JSON格式边数据 #

WITH target_nodes AS (
    SELECT id FROM objects 
    WHERE description LIKE '%birds%' 
       OR description LIKE '%lex%' 
       OR description LIKE '%john%'
)
SELECT 
    json_build_object(
        'source', ors.object_id,
        'target', ors.child_id,
        'type', ors.type
    ) AS edge
FROM object_relationships ors
JOIN target_nodes tn_source ON tn_source.id = ors.object_id
JOIN target_nodes tn_target ON tn_target.id = ors.child_id;

方法二：输出文本格式边数据（匹配预期的节点关联形式） #

WITH target_nodes AS (
    SELECT id FROM objects 
    WHERE description LIKE '%birds%' 
       OR description LIKE '%lex%' 
       OR description LIKE '%john%'
)
SELECT 
    CONCAT(ors.object_id, ' - ', ors.type, ' - ', ors.child_id) AS edge
FROM object_relationships ors
WHERE 
    ors.object_id IN (SELECT id FROM target_nodes)
    AND ors.child_id IN (SELECT id FROM target_nodes);

逻辑说明 #

1. 用CTEtarget_nodes预先获取所有符合条件的节点ID；
2. 关联object_relationships表时，仅保留源节点和目标节点都在target_nodes中的记录，彻底避免外部节点混入；
3. 两种查询分别适配JSON结构化输出和可读性文本输出，可根据业务需求选择。

内容的提问来源于stack exchange，提问作者C-mon