sql - 有没有更好的方法来区分没有 child 或无效的 parent 身份？

我想检索给定 parent 的所有 child 。需要区分三种情况:

有 child ，以JSON数组形式返回。

没有子级但父级存在，返回一个空的 JSON 数组。

父级不存在，不返回任何东西。

带 child 很容易:

select json_agg(child)
from   child
where  child.parent_id = :parent_id

json_agg 的一个问题是当没有行时它总是返回 null。因此，以下将返回值为 null 而不是 0 行的一行:

select json_agg(child)
from   child
where  false

这增加了区分这三种情况的复杂性。为了解决这个问题，我想出了以下内容:

with
parent as
(
    select id
    from   parent
    where  id = :parent_id
),
result as
(
    select     coalesce(json_agg(child), '[]')
    from       child
    inner join parent
    on         child.parent_id = parent.id
)
select result.*
from   result
where  exists(select * from parent)

这似乎有效，但它相当笨拙。我想知道是否有更好的解决方案。有任何想法吗？

更新

根据要求，这是我想要的:

[{...}, {...}, ...] 当有 child 时。

[] 当没有 child 但 parent 存在时。

no rows 当父节点不存在时。

最佳答案

将 case 与派生表中的聚合一起使用，例如:

select children
from (
    select p.id, case when min(c.id) is null then '[]'::json else json_agg(c) end as children
    from parent p
    left join child c on c.parent_id = p.id
    group by p.id
    ) s
where id = :parent_id

关于sql - 有没有更好的方法来区分没有 child 或无效的 parent 身份？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48041526/