3

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

3
  • look this is the data I've got, not my choice. that's the source.
    – zabumba
    Commented Nov 2, 2016 at 11:41
  • 1
    But are you able to insert the data into a properly normalized table? Commented Nov 2, 2016 at 11:45
  • 2
    Go and check the docs about array functions. Commented Nov 2, 2016 at 12:04

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.