Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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'
Add Comment
Please, Sign In to add comment