PostgreSQL：如何将article表jsonb标签名更新为标签ID？

场景说明 #

现有两张表：

• tag表存储标签ID与名称
• article表的tags字段为jsonb类型，存储的是标签名称数组，需要批量替换为tag表中对应的标签ID数组

批量更新SQL语句 #

UPDATE article a
SET tags = (
    SELECT jsonb_agg(t.id)
    FROM jsonb_array_text(a.tags) AS tag_name
    JOIN tag t ON t.name = tag_name
)
WHERE a.tags IS NOT NULL AND a.tags != '[]'::jsonb;

语句解释 #

1. jsonb_array_text(a.tags)：将article表中每条记录的tags jsonb数组拆分为单独的标签名称文本行
2. JOIN tag t ON t.name = tag_name：通过标签名称关联tag表，获取对应的标签ID
3. jsonb_agg(t.id)：将匹配到的标签ID重新聚合为jsonb数组
4. WHERE子句：过滤掉tags为空或空数组的记录，避免无意义更新

补充说明 #

• 如果存在标签名称在tag表中不存在的情况，上述语句会自动忽略这些名称，最终tags数组只包含能匹配到ID的标签
• 若需要保留未匹配的名称（不推荐，不符合需求），可改用LEFT JOIN并处理NULL值，例如：

UPDATE article a
SET tags = (
    SELECT jsonb_agg(COALESCE(t.id, tag_name::text))
    FROM jsonb_array_text(a.tags) AS tag_name
    LEFT JOIN tag t ON t.name = tag_name
)
WHERE a.tags IS NOT NULL AND a.tags != '[]'::jsonb;

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