Question 114272 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using unnest to join in Postgres

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!

Tags:

4

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 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';
    Login or Signup to reply.
  3. 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'
    Login or Signup to reply.
  4. 0

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search