11

I have a table test column with int arrays and values like {1000,4000,6000} or {1000} or {1000,4000} called ekw. These values match to a description string in another table 

tab: test
id | name   | ekw
-----------------
 1 |  One   | {1000}
 2 |  Two   | {1000,4000}
 3 |  Three | {1000,4000,6000}

tab: ekwdesc
id | value  | desc
-----------------
 1 |  1000  | Max
 2 |  2000  | Tim
 3 |  3000  | Rita
 5 |  4000  | Sven
 6 |  5000  | Tom
 7 |  6000  | Bob

is it possible to select these columns and print the strings?

something like:

select name, ekw from test, ekwdesc

I would like to see this result:

id | name   | ekwdesc
-----------------
 1 |  One   | Max
 2 |  Two   | Max, Sven
 3 |  Three | Max, Sven, Bob

I tried with IN and ANY but couldn't get it to work.

matzeihnsein
  • 678
  • 7
  • 16

1 Answers1

19

You had the right idea to use the any operator for the join. Once the join is complete, all that's left is to use string_agg to transform the result to the format you want:

SELECT   name, STRING_AGG(description, ', ')
FROM     test
JOIN     ekwdesc ON ekwdesc.value = ANY(test.ekw)
GROUP BY name

See the attached SQLFiddle for an executable example.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Does this assure that the description tags will be returned in the right order -- the order of the values in the ekw array? – Sigfried Jan 05 '17 at 13:08
  • @Sigfried Nope. – John Smith Apr 12 '17 at 19:12
  • Can this solution be changed to work in case, where main table has json fields? as you know, you can't use group by for json columns. – eyeinthebrick Apr 18 '17 at 09:57
  • @eyeinthebrick Not sure I understand what you're asking. Could you perhaps post a new question with the new problem statement? – Mureinik Apr 18 '17 at 13:40
  • Only similar question and answer found, that too doesn't work. Array should be on right side error. Checked so many things nothing worked for my jsonb[] case. – Lalit Fauzdar Aug 09 '22 at 13:14