Using unnest to join in Postgres

Question

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!

score 0 · Answer 1 · answered Oct 12 '22 at 06:02

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

score 0 · Answer 2 · answered Oct 12 '22 at 06:10

0

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';

answered Oct 12 '22 at 06:10

Jan Marek

10,390
3
21
19

Bergi · Answer 3 · 2022-10-12T06:20:57.833

0

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'

edited Oct 12 '22 at 06:20

answered Oct 12 '22 at 06:15

Bergi

630,263
148
957
1,375

score 0 · Answer 4 · answered Oct 12 '22 at 16:31

You can use jsonb_array_elements_text with a (implicit) lateral join:

SELECT
  likes.likes_id,
  likes.name,
  likes.location,
  shares.timestamp
FROM
  shares,
  jsonb_array_elements_text(shares.likes_id_array) AS share_likes(id),
  likes
WHERE
  likes.likes_id = share_likes.id AND
  shares.id = '12345_6789';

Output:

┌──────────┬──────┬──────────┬─────────────────────┐
│ likes_id │ name │ location │      timestamp      │
├──────────┼──────┼──────────┼─────────────────────┤
│ xxx      │ aaaa │ nice     │ 2022-10-12 11:32:39 │
│ yyy      │ mmmm │ place    │ 2022-10-12 11:32:39 │
│ zzz      │ dddd │ here     │ 2022-10-12 11:32:39 │
└──────────┴──────┴──────────┴─────────────────────┘
(3 rows)

Or if you want to make the lateral join explicit (notice the addition of the LATERAL keyword):

SELECT
  likes.likes_id,
  likes.name,
  likes.location,
  shares.timestamp
FROM
  shares,
  LATERAL jsonb_array_elements_text(shares.likes_id_array) AS share_likes(id),
  likes
WHERE
  likes.likes_id = share_likes.id AND
  shares.id = '12345_6789';

Using unnest to join in Postgres

4 Answers4