PostgreSQL如何通过jsonb字段的重叠值实现多表关联？

错误原因说明 #

1. 你的SQL触发报错的直接原因是sports表中存在object为null、以及object不含match_ids键的记录，直接调用->运算符取值时返回值类型不符合运算要求，触发类型匹配错误。
2. 你使用的<@运算符是判断左侧数组完全属于右侧数组的子集，不符合「存在至少一个共同元素」的需求，且你没有处理jsonb数组中数字类型和字符串类型值不匹配的问题（比如112和"112"在jsonb中默认是不同值）。

可行SQL语句 #

SELECT 
  sports.id AS sports_id,
  venues.id AS venue_id,
  venues.match_ids
FROM venues
INNER JOIN sports 
  -- 先过滤出object有效且存在match_ids键的sports记录，避免取值报错
  ON sports.object IS NOT NULL
  AND sports.object ? 'match_ids'
  -- 将两侧jsonb数组统一转成文本数组后，用&&运算符判断是否有共同元素
  AND ARRAY(SELECT jsonb_array_elements_text(venues.match_ids)) 
    && 
    ARRAY(SELECT jsonb_array_elements_text(sports.object -> 'match_ids'));

执行结果验证 #

上述SQL执行后返回的结果和你给出的预期完全一致：

内容的提问来源于stack exchange，提问作者Hiroki