PostgreSQL 9.5多表关联生成单条JSON状态结果的实现问题

可以通过PostgreSQL的JSON构造函数结合存在性判断，直接生成你需要的单行JSON结果，以下提供两种可行的SQL写法：

写法一：使用EXISTS子查询判断存在性 #

SELECT json_build_object(
    'A', a.isStatusA AND EXISTS(SELECT 1 FROM B WHERE status = 'A' AND a_id = a.ID),
    'B', a.isStatusB AND EXISTS(SELECT 1 FROM B WHERE status = 'B' AND a_id = a.ID),
    'C', a.isStatusC AND EXISTS(SELECT 1 FROM B WHERE status = 'C' AND a_id = a.ID)
) AS result
FROM A a
WHERE a.ID = 0;

逻辑说明 #

• json_build_object：PostgreSQL 9.5支持的函数，直接构造键值对形式的JSON对象；
• 每个状态的判断逻辑：同时满足表A对应状态字段为true，且表B中存在匹配当前A表ID和对应status的记录（EXISTS子查询会高效判断记录是否存在，无需返回具体数据）；
• WHERE条件过滤目标ID，确保仅返回表A中那条目标数据的结果。

写法二：先聚合表B状态再判断（适合表B数据较多的场景） #

WITH b_status AS (
    -- 先收集目标a_id对应的所有status到数组中
    SELECT array_agg(DISTINCT status) AS status_list
    FROM B
    WHERE a_id = 0
)
SELECT json_build_object(
    'A', a.isStatusA AND 'A' = ANY(bs.status_list),
    'B', a.isStatusB AND 'B' = ANY(bs.status_list),
    'C', a.isStatusC AND 'C' = ANY(bs.status_list)
) AS result
FROM A a
CROSS JOIN b_status bs
WHERE a.ID = 0;

逻辑说明 #

• 用CTE（WITH子句）先查询一次表B，把目标a_id对应的所有status聚合为数组，避免多次查询表B；
• 使用= ANY()判断对应状态是否存在于数组中，结合表A的状态字段得到最终布尔值。

两种写法执行后都会返回预期结果：{"A": true, "B": false, "C": true}

内容的提问来源于stack exchange，提问作者Sebastian Halik