PostgreSQL如何使用array_agg和数组包含运算符匹配两张表姓名数据
PostgreSQL双表姓名关联查询解决方案
1. 生成t2全姓名字段的一维数组
你之前的写法报错是因为将多个嵌套数组直接塞入同一个ARRAY构造器,导致出现text和text[]类型不匹配的问题,且硬编码键名无法适配动态场景。以下是支持动态键名的一维数组生成语句:
SELECT t2.id AS t2_id, t2.feat_id, -- 合并所有名称为一维文本数组,自动过滤空值 ARRAY( SELECT unnest( ARRAY[t2.name_1, t2.name_2] -- 提取alt_names中所有键对应的值,无需指定键名 || ARRAY(SELECT value FROM jsonb_each_text(t2.alt_names->0)) ) WHERE unnest IS NOT NULL ) AS t2_names FROM t2;
生成的t2_names格式完全符合预期,示例结果:
{'Natasha Romanoff','Black Widow','Black Widow','Natalia Alianovna Romanova','Наталья "Наташа Романоф"'}
2. 数组匹配与关联表生成
2.1 t1数组构造
t1单条记录本身对应一组姓名,无需聚合可直接构造数组:
SELECT id AS t1_id, fid AS t1_fid, name_1 AS t1_name_1, name_rank, ARRAY[name_1, name_2] AS t1_names FROM t1;
2.2 匹配规则与关联表生成
使用数组重叠运算符&&判断两组姓名是否存在共同元素,用DISTINCT ON取每个t2匹配到的name_rank最小的t1记录:
-- 生成最终关联表 CREATE TABLE t1_linked_t2 AS SELECT DISTINCT ON (t2.id) t1.id AS id, t1.fid AS t1_fid, t1.name_1 AS t1_name_1, t1.name_rank, t2.name_1 AS t2_name, t2.id AS t2_id FROM t1 -- 先关联特征id缩小匹配范围,大幅提升查询性能 JOIN t2 ON t1.fid = t2.feat_id WHERE -- 判断两个姓名数组是否存在共同元素 ARRAY[t1.name_1, t1.name_2] && ( SELECT ARRAY( SELECT unnest( ARRAY[t2.name_1, t2.name_2] || ARRAY(SELECT value FROM jsonb_each_text(t2.alt_names->0)) ) WHERE unnest IS NOT NULL ) ) -- 按t2分组,优先取name_rank最小的匹配记录 ORDER BY t2.id, t1.name_rank ASC;
查询关联表验证结果:
SELECT * FROM t1_linked_t2;
内容的提问来源于stack exchange,提问作者Holly
相关产品推荐
相关产品推荐

