首先在CTE t1和t2中扁平化tableA和tableB的json字段-然后执行一个例程join。请注意，这种表设计效率极低，而且很难使用。

with t1 as 
(
 select jsonfield::json ->> 'uid' as uid, jsonfield::json as json_a from tablea
), 
t2 as 
(
 select json_b, json_b ->> 'uid' as uid, json_b ->> 'user_name' as user_name 
 from (select json_array_elements(jsonarr::json) json_b from tableb) as t
)
select uid, user_name, json_a, json_b 
from t1 
join t2 using (uid);

DB-小提琴demo

试试这样的方法：

SELECT *
  FROM tableA AS a
 INNER JOIN 
     ( tableB AS b
       CROSS JOIN LATERAL jsonb_path_query(b.text_column :: jsonb, '$[*]') AS c(json_value)
     ) AS d
    ON a.text_column :: jsonb->'uid' = d.json_value->'uid'