How to map comma separated numbers to field names in another database table and concatenate the result?

Jwan622 0
Viewed 33

I have a column of integer values that represent strings in another table. For example, say one column (let's call it pets in the users table) has values like [1,3,4] and another table called pet_names has rows like:

Example Id Name
1 1 dog
2 2 cat
3 3 bird
4 4 lizard

If the array value in the user table is [1,2,4]. I want a query that returns the string: "dog, birds, lizard" with spaces after the comma. How does one do this in postgres?

EDIT

Oh so sorry. since it's redshift it doesn't store the array data as an array. It's actually a string like "[1,2,4]".

amazon-redshift postgresql
1 Answers
  1. You will need to unnest those array values into rows:
SELECT pet_id
FROM pets, unnest(pets_id_array_col) as pet_id
  1. Select from pet_names using the result set from above:
SELECT Name
FROM pets
WHERE id in 
    (
        SELECT pet_id
        FROM pets, unnest(pets_id_array_col) as pet_id
    )
  1. Finally you STRING_AGG() those names together:
SELECT STRING_AGG(Name, ', ') as Names
FROM pets
WHERE id in 
    (
        SELECT pet_id
        FROM pets, unnest(pets_id_array_col) as pet_id
    )

This is a little ugly because storing data in an array/comma delimited list isn't a great choice, especially since you need to join on these values.

JNevill0
Related