0

I have a table of people : id, person_name

I have a table of tags: id, tag_name

there is a junction table for the tags that connects people and tags: id, person_id, tag_id

I have a query to get a group of people based on different search criteria so it's being built up dynamically and what I would like to do is within that query - for each person - I'd like to be able to grab all of the tag names associated w/ that person. I'd like to grab all the associated tags and stick them into one column for each person row (preferably as an array or object). What is the general pattern to be able to return a group of stuff as one column when cycling through rows like this. I'm having trouble figuring out what to search for to find a generalized answer online. I'm using postgres.

0
1

As others have mentioned, your goal is to denormalize the data and is not a usual use case for a relational database system, but it is certainly achievable. So if you really want to accomplish that, what you'd want to Google is something along the lines of "PostgreSQL concatenate multiple rows". The goal usually is to create a comma separated values list (CSV or something of a similar structure).

Doing so actually led me to this StackOverflow answer on using the string_agg() function in PostgreSQL.

For historical reference, here is Erwin's example from the aforementioned StackOverflow answer, leveraging string_agg() to smush multiple Actor values into a single field per Movie row in a table of Movies:

SELECT movie, string_agg(actor, ', ') AS actor_list
FROM tbl
GROUP BY 1;

In your case, you'd want to JOIN your people to your tags table (via your junction table). From the above example movie would be equivalent to your person_name field, and actor would be equivalent to your tag_name field. A query likely similar to this is what you're looking for:

SELECT P.person_name, string_agg(T.tag_name, ', ') AS tag_list
FROM people P
LEFT JOIN peopleTagJunctionTable JT
    ON P.id = JT.person_id
LEFT JOIN tags T
    ON JT.tag_id = T.id
GROUP BY P.person_name;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.