Join result of set-returning function (json_array_elements) with table column

Question

Let's say I have two tables:

User_Combination

+--------+----------------+
|   id   |   combination  |
+--------+----------------+
|    6   |     [1, 2]     |
|    9   |     [2, 3]     |
+--------+----------------+

Colors

+--------+----------------+
|   id   |   color        |
+--------+----------------+
|    1   |       Blue     |
|    2   |       Yellow   |
|    3   |       Green    |
+--------+----------------+

I'm trying to join the result of json_array_elements(color) with the id of Elements. For example, the result of

select json_array_elements(color) as CombinationID
from User_Combination where id = 6;

is

+-------------------+
|   CombinationID   |
+-------------------+
|    1              |
|    2              |
+-------------------+

I'm unable to join CombinationID with Colors.id. When I try an SQL command such as:

select json_array_elements(article_data) AS articlesInOutfits (color) as CombinationID
from User_Combination uc JOIN Colors co ON co.id = articlesInOutfits;

or

select json_array_elements(article_data) AS articlesInOutfits (color) as CombinationID
from User_Combination uc JOIN Colors co ON co.id = uc.articlesInOutfits;

It says articlesInOutfits does not exist.
Any advice?

Answer 1

Use unnest() to get unpacked combinations:

select id, unnest(combination) cid
from user_combination;

 id | cid 
----+-----
  6 |   1
  6 |   2
  9 |   2
  9 |   3
(4 rows)    

Use unpacked cids to join with colors:

select u.id, color
from (
    select id, unnest(combination) cid
    from user_combination
    ) u
join colors c
on cid = c.id;

 id | color  
----+--------
  6 | Blue
  6 | Yellow
  9 | Yellow
  9 | Green
(4 rows)

Use an aggregate function (e.g. json_agg()) to get joined colors aggregated for a user:

select u.id, json_agg(color)
from (
    select id, unnest(combination) cid
    from user_combination
    ) u
join colors c
on cid = c.id
group by 1;

 id |      json_agg       
----+---------------------
  9 | ["Yellow", "Green"]
  6 | ["Blue", "Yellow"]
(2 rows)    

---

If combination is of type json you should use json_array_elements() in a lateral join:

select u.id, json_agg(color)
from (
    select id, cid
    from user_combination,
    lateral json_array_elements(combination) cid
    ) u 
join colors c
on cid::text::int = c.id
group by 1;