Joining Tags as an Array-CodePudding

I have this:

posts
- id
- title
- ...
tags
- pid (fk to posts.id)
- name

And I would like to join my posts so that I have an array of tags in my posts record:

id | title |     | tags
___|_______|_____|_____________________
1  | me    | ... | ["you", "him"]
2  | you   | ... | ["him", "something"]
...

How can I accomplish this. I don't want to over fetch, either.

Here is what I have so far:

CREATE OR REPLACE VIEW posts_tags AS
SELECT posts.* FROM posts
JOIN array_agg(
  SELECT tags.name FROM tags
  WHERE posts.id = tags.pid
)

Not sure how the array part works in SQL or how to finish this query.

CodePudding user response：

The query with fixed syntax:

SELECT *
FROM   posts p
JOIN  (
   SELECT pid AS id, array_agg(name) AS post_tags
   FROM   tags
   GROUP  BY pid
   ) t USING (id);

It's faster to aggregate before you join.

For a small selection of posts, a LATERAL subquery, or a correlated subquery in the SELECT list can be faster. See: