How to create a custom view (using lookup) from aggregated data 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        =&gt; {1,2,3}
2  | 4,5,6        =&gt; {4,5,6}
3  | 3,1,4        =&gt; {3,1,4}


after what I tried array_to_json(anyarray [, pretty_bool]) doc functions-json ...
To disaggregate
SELECT id, 
   json_array_elements(array_to_json(string_to_array(csv_user_ids, ',')))
FROM groups


string_to_array converts the CSV string into ARRAY
array_to_json   converts ARRAY into JSON
json_array_elements each element into a separate row

next I just need to aggregate again.