Using unnest to join in Postgres-CodePudding

Appreciate this is a simple use case but having difficulty doing a join in Postgres using an array.

I have two tables:

table: shares

   id            |  likes_id_array  timestamp  share_site
----------------- ----------------- ---------- -----------
    12345_6789   | [xxx, yyy , zzz]|  date1   |  fb
    abcde_wxyz   | [vbd, fka, fhx] |  date2   |  tw


table: likes


   likes_id     | name     |  location    
-------- ------- ---------- -----
    xxx         |   aaaa   | nice
    fpg         |   bbbb   | dfpb
    yyy         |   mmmm   | place 
    dhf         |   cccc   | fiwk
    zzz         |   dddd   | here


desired - a result set based on shares.id = 12345_6789:

   likes_id     | name     |  location  |  timestamp    
-------- ------- ---------- ------------ -----------
    xxx         |   aaaa   | nice       |   date1
    yyy         |   mmmm   | place      |   date1
    zzz         |   dddd   | here       |   date1

the first step is using unnest() for the likes_id_array:

SELECT unnest(likes_id_array) as i FROM shares
WHERE id = '12345_6789'

but I can't figure out how to join the results set this produces, with the likes table on likes_id. Any help would be much appreciated!

CodePudding user response：

You can create a CTE with your query with the likes identifiers, and then make a regular inner join with the table of likes

with like_ids as (
  select
      unnest(likes_id_array) as like_id
  from shares
     where id = '12345_6789'
) 
select
    likes_id,
    name,
    location
from likes 
   inner join like_ids
on likes.likes_id = like_ids.like_id

Demo

CodePudding user response：

You can use ANY:

SELECT a.*, b.timestamp FROM likes a JOIN shares b ON a.likes_id = ANY(b.likes_id_array) WHERE id = '12345_6789';

CodePudding user response：

You could do this with subqueries or a CTE, but the easiest way is to call the unnest function not in the SELECT clause but as a table expression in the FROM clause:

SELECT likes.*, shares.timestamp
FROM shares, unnest(likes_id_array) as arr(likes_id)
JOIN likes USING (likes_id)
WHERE shares.id = '12345_6789'