How to create a custom view (using lookup) from aggregated data

Question

I have data imported into a postgresql database:

groups (data import)
id | csv_user_ids
1 | 1,2,3
2 | 4,5,6
3 | 3,1,4

... and a lookup table for user names:

users (lookup)
id | name
1 | one
2 | two
3 | three
4 | four
5 | five
6 | six

I need to build a VIEW of groups with JSON column matching the user IDs as such

groups_view
id | json_array_users
1 | [{ 'id': 1, 'name': 'one'}, { 'id': 2, 'name': 'two'}, { 'id': 3, 'name': 'three'}]
2 | [{ 'id': 4, 'name': 'four'}, { 'id': 5, 'name': 'five'}, { 'id': 6, 'name': 'six'}]
3 | [{ 'id': 3, 'name': 'three'}, { 'id': 1, 'name': 'one'}, { 'id': 4, 'name': 'four'}]

How do I do that?

I guess I'd need to disaggregate rows in groups before aggregating into my new view again?

JSON result is an array.

source:

groups (data import)
id | csv_user_ids
1 | 1,2,3
2 | 4,5,6
3 | 3,1,4

to:

group_user
group_id | user_id
1 | 1
1 | 2
1 | 3
2 | 4
2 | 5
2 | 6
3 | 3
3 | 1
3 | 6

How do I do that in SQL?

With string_to_array(text, text [, text]) from doc functions array, I was able to turn the CSV string into an array e.g. {1,2,3}

groups (data import)
id | csv_user_ids 
1 | 1,2,3 => {1,2,3}
2 | 4,5,6 => {4,5,6}
3 | 3,1,4 => {3,1,4}

after what I tried array_to_json(anyarray [, pretty_bool]) doc functions-json ...

• zabumba: look this is the data I've got, not my choice. that's the source.
• András Váczi: But are you able to insert the data into a properly normalized table?
• András Váczi: Go and check the docs about array functions.

Answer 1

To disaggregate

SELECT id, 
 json_array_elements(array_to_json(string_to_array(csv_user_ids, ',')))
FROM groups

1. string_to_array converts the CSV string into ARRAY
2. array_to_json converts ARRAY into JSON
3. json_array_elements each element into a separate row

next I just need to aggregate again.