pg_experiments.sql

pg_experiments.sql

-- my table is called 'document', is has a type (text) and a document (jsonb) colum


select d1.*,
(
  select to_json(t) from
  (select * from document d2
  where type = 'author'
  and d2.id = (d1.body->>'author_id')::int
  ) t
) as author,
(
  select to_json(array_agg(t)) from
  (select * from document d3
  where type = 'comment'
  and (d3.body->>'article_id')::int = d1.id
  ) t
) as comments

from document d1
where type = 'article'