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

  • look this is the data I've got, not my choice. that's the source. – zabumba Nov 2 '16 at 11:41
  • 1
    But are you able to insert the data into a properly normalized table? – dezso Nov 2 '16 at 11:45
  • 2
    Go and check the docs about array functions. – dezso Nov 2 '16 at 12:04
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, privacy policy and cookie policy

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