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 ...