PostgreSQL根据countries表解析数组中的国家

Question

我们有内容表和国家/地区表。国家非常简单：country_name列定义为字符串：阿尔巴尼亚，比利时中国丹麦等…

Content是一个包含50万行的表，其中包含各种数据，countries列定义为arraytext[]。每个值都有多个国家/地区，如：{丹麦、芬兰、法国、德国、爱尔兰、英国、意大利、荷兰、波兰、俄罗斯、西班牙、瑞典、澳大利亚、巴西、加拿大、中国、印度、印度尼西亚、日本、马来西亚、越南、墨西哥、韩国、泰国、美国、新加坡、阿联酋

内部团队的更新是针对一千条记录的，我们不确定国家是否都拼写正确。因此，任务是与country表中的country_name进行对账。

我正在做replace(replace(country_array::text,'{',''),'}','') as country_text，并考虑做UNPIVOT来对照国家表检查每一列。有没有其他更简单的方法可以确保内容表中的国家/地区数组具有国家/地区表中的有效国家/地区名称？

谢谢

Answer 1

您可以将每个数组的unnest()添加到一组行中，并确保所有值都出现在country表中。以下查询为您提供引用表中缺少的数组元素：

select *
from 
content c
cross join lateral unnest(c.countries) as t(country_name)
left join country y on y.country_name = t.country_name
where y.country_name is null

DB Fiddle演示

国家/地区表：

id|country_name-：|：-----------1|阿尔巴尼亚2|denmark

内容表：

id|国家-：|：----------------1|{阿尔巴尼亚，丹麦}1|{阿尔巴尼亚，法国}

查询结果：

id|country|country_name-：|：---------------|：-----------1|{阿尔巴尼亚，法国}|法国

Answer 2

如果你怀疑某些国家的拼写不正确，那么毫无疑问会有这样的例子。

首先获取参考表中而非的国家列表：

select c_country, count(*)
from content c cross join lateral
unnnest(c.countries) c_country left join
countries co
on co.country_name = c_country
where co.country_name is not null
group by c_country
order by count(*) desc;

然后，您可以进入并修复数据。

将值存储在数组中并没有错。但是，如果您从头开始设计数据库，我可能会推荐使用contentCountries表和countryId。这将确保明确的关系。

在您的情况下，您可能应该修复摄取过程，以便在输入时知道值是正确的。这可能就足够了，因为你已经有很多数据，只需要修复它