general design pattern for smushing rows into one column

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply