Home:ALL Converter>Storing array of IDs and how to correctly unpack them on a select out

Storing array of IDs and how to correctly unpack them on a select out

Ask Time:2018-06-08T21:53:59         Author:StaticMaine

Json Formatter

This might be partly a design question (new to PostgreSQL) as well.

I have three tables - Users, Groups and User_Group. User_Group represents a combination of 1 user_id being linked to 0..X Group IDs.

The tables are as simple as you think (for now, building out this thing):

User: ID, Name, ....

Group: ID, Name, ...

User_Group: UserID, GroupID int[], ...

So right now, the GroupID field in User_Group is an Integer array. UserID 1 has a value of {1,2,10,19,28} for example.

Goal: In my UI, I need to represent that list as the group names (ie: {Group1, Group2, Group10, Group19, Group28}).

So, because I am new to PostgreSQL, I'm researching and a couple ideas pop into my mind - unnest, ANY and array replacement. All scream performance issues to me, but I might be wrong (this is the design question, is it smart to store array?)

My query right now:

select 
    u.*,
    g.group_ids
from users u
left join user_group g
    on u.id = g.user_id

Piece I'm trying to figure out how to push into:

select ug.group_id 
from (select unnest(group_ids) group_id FROM user_group) as ug
left join groups g 
    on g.id = ug.group_id

This will just result in (obviously) an additional row for each group ID the person is associated with.

Which is the best way to do this?

Author:StaticMaine,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/50762224/storing-array-of-ids-and-how-to-correctly-unpack-them-on-a-select-out
Cetin Basoz :

( Personally I would have a column on Users table as Groups (int array) but your choice is fine too). \n\nIt would look like (I used table and field names off the top of my head, slightly modified than yours):\n\nselect u.*, g.Name as GroupName\nfrom users u\nleft join usergroups ug on ug.UserId = u.UserId\nleft join groups g on g.groupId = ANY( ug.groups );\n\n\nUpdate: I might have misunderstood your need. Maybe you meant this:\n\nselect u.*, \n (select string_agg(g.name,',') \n from groups g \n inner join usergroups ug on ug.groupId = g.GroupId\n where ug.UserId = u.UserID and \n g.groupId = ANY( ug.groups )) as Groups\nfrom users u;\n",
2018-06-08T14:27:52
yy