PostgreSQL单查询关联两表JSONB数组匹配取数方法

完全可以通过单条SQL实现该需求，无需拆分多步操作。

实现思路 #

利用PostgreSQL内置的jsonb_array_elements函数，将两张表中存储的JSONB数组拆解为逐行独立的元素记录，再按IP匹配规则做关联过滤，直接提取需要的字段即可。

注：你描述中初始提的JSONB字段名为members，后续示例用的字段名为data，以下SQL统一使用示例中的data字段，若实际字段为members直接替换对应字段名即可。

可直接运行的SQL代码 #

SELECT
  t1_item ->> 'computer' AS computer,
  t1_item ->> 'tag' AS tag,
  t2_item ->> 'name' AS name
FROM
  table1,
  jsonb_array_elements(table1.data) AS t1_item,
  table2,
  jsonb_array_elements(table2.data) AS t2_item
WHERE
  table1.id = 1
  AND table2.id = 1
  AND t1_item ->> 'computer' = t2_item ->> 'IP address';

逻辑说明 #

• jsonb_array_elements：JSONB数组处理函数，会将输入的JSONB数组中每个元素拆分为独立的行记录
• ->>操作符：JSONB专用取值操作符，用于提取指定键对应的文本格式值，避免额外类型转换
• 因为你的两张表都仅存储id=1的单行数据，拆解后通过等值条件过滤，只会保留IP匹配的条目，table1中2.2.2.2对应的cow条目因为table2无匹配IP，不会出现在最终结果中，完全匹配你给出的期望结果。

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