您可以使用Like标识符用查询创建一个CTE，然后与Like表进行常规的内部连接

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

您可以使用ANY：

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

您可以使用子查询或CTE来执行此操作，但最简单的方法是不在SELECT子句中调用unnest函数，而是在FROM子句中将其作为表表达式调用：

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

可以将jsonb_array_elements_text与(隐式)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';

产出：

┌──────────┬──────┬──────────┬─────────────────────┐
│ 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)

或者，如果要使横向联接显式(请注意添加了关键字LATERAL)：

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