Postgres join as inner json

Question

Just trying to do a simple inner join as json.

model

posts (author is fk to profiles.id)
  id
  author
  title
  ...
profiles
  id
  displayName
  ...

Trying to do a simple join so that:

select * from posts
(join profiles on profiles.id = posts.author -- as json?)

so that my result would look like:

id	title	author	..
1	some title 1	{ id: 02xx, displayName: 'Jon', username: 'jon1' }
2	some title 2	{ id: 02xx, displayName: 'Jon', username: 'jon1' }

I feel like there is a very simple way to do this?

J

Answer 1

Well I seem to have found two ways to do this, although I imagine there are many. If you know of another way, please post it here.

array_to_json()

select posts.*, array_to_json(array_agg(profiles.*)) as author from posts
join profiles on profiles.id = posts.author
group by posts.id

Not sure why I need group by here, but this is good if you're joining several items.

row_to_json()

select posts.*, row_to_json(profiles.*) as author from posts
join profiles on profiles.id = posts.author

This seems to be the best in my use case.

Please most more if you know of them!

Thanks,

J