sql - 从联接表创建 JSON

Question

如何从这个语句创建 JSON(B):

SELECT *
FROM foo
LEFT JOIN bar
  USING (id)

我的解决方案目前看起来是这样的:

SELECT to_jsonb(foo) || coalesce(to_jsonb(bar), '{}')
FROM foo
LEFT JOIN bar
  USING (id)

对于每个连接的表，这变得更丑陋，例如:

SELECT to_jsonb(foo) || coalesce(to_jsonb(bar), '{}') || coalesce(to_jsonb(baz), '{}')
FROM foo
LEFT JOIN bar
  USING (id)
LEFT JOIN baz
  USING (id)

我想要这样的东西:

SELECT to_jsonb(*)
FROM foo
LEFT JOIN bar
  USING (id)

但这给了我:

[42883] ERROR: function to_jsonb() does not exist

Answer 1

第三个查询生成 bar 和 baz 中具有相同 id 的所有行的笛卡尔积。无论是否有意，您都可以使用派生表，将到 JSON 的转换移动到外部查询。

select to_jsonb(q)
from (
    select *
    from foo
    left join bar using(id)
    left join baz using(id)
    ) q