如何在PostgreSQL中从Bio提取数据库中存在的提及内容

需求实现思路 #

先从目标用户的bio字段中提取所有@开头的提及内容，再与profile表的name字段做关联匹配，仅保留确实存在的用户名，排除无效提及。

实现查询语句 #

SELECT 
    p.name,
    -- 将有效提及聚合为数组，DISTINCT去重
    array_agg(DISTINCT extracted_mentions.mention) AS valid_mentions
FROM 
    profile p
-- 横向关联提取bio中的所有@提及
CROSS JOIN LATERAL (
    SELECT 
        -- 去掉@符号，得到纯用户名
        regexp_replace(mention_match, '^@', '', 'g') AS mention
    FROM 
        regexp_matches(p.bio, '@[^@\s]+', 'g') AS m(mention_match)
) AS extracted_mentions
-- 关联profile表，筛选存在的用户名
JOIN profile p2 ON extracted_mentions.mention = p2.name
WHERE 
    p.name = 'John Doe'
GROUP BY 
    p.name;

语句说明 #

1. regexp_matches(p.bio, '@[^@\s]+', 'g')：全局匹配bio中所有@开头、直到下一个空格或@结束的内容，提取所有候选提及。
2. regexp_replace(mention_match, '^@', '', 'g')：移除提及内容开头的@符号，得到可与name字段匹配的纯用户名。
3. JOIN profile p2：通过用户名关联，仅保留在profile表中存在的有效提及。
4. array_agg(DISTINCT ...)：将同一用户的多个重复提及去重后聚合为数组，若需要每个提及单独成行，可替换为unnest(array_agg(DISTINCT ...))。

可选：拆分提及为单独行 #

如果希望每个有效提及作为单独的行返回，可使用以下查询：

SELECT DISTINCT
    p.name,
    extracted_mentions.mention AS valid_mention
FROM 
    profile p
CROSS JOIN LATERAL (
    SELECT 
        regexp_replace(mention_match, '^@', '', 'g') AS mention
    FROM 
        regexp_matches(p.bio, '@[^@\s]+', 'g') AS m(mention_match)
) AS extracted_mentions
JOIN profile p2 ON extracted_mentions.mention = p2.name
WHERE 
    p.name = 'John Doe';

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