PostgreSQL中如何从字符串提取指定目标短语与字段值

假设存储原始日志内容的字段名为raw_log，所属表为audit_records，可根据你的实际表结构调整字段名和表名。

核心思路 #

你的原始日志由两部分组成：顶层的单行键值对、末尾的JSON格式AUDITDATA字段，分别处理两类内容即可：

• 顶层非JSON字段用正则表达式直接匹配提取，同步做格式清洗
• AUDITDATA字段先还原转义字符转为标准JSON类型，再直接提取对应属性

完整实现SQL #

SELECT
  -- 提取CREATION_DATE
  'CREATION_DATE: ' || substring(raw_log FROM 'CREATION_DATE:\s*([^\n]+)') AS creation_date_line,
  -- 提取USER_EMAIL并处理首字母大写、去除单引号
  'USER_EMAIL: ' || upper(left(trim('''' FROM substring(raw_log FROM 'USER_EMAIL:\s*''([^\n]+)')), 1)) 
    || right(trim('''' FROM substring(raw_log FROM 'USER_EMAIL:\s*''([^\n]+)')), -1) AS user_email_line,
  -- 提取ACTION并处理驼峰转空格分隔、去除单引号
  'ACTION: ' || trim(regexp_replace(trim('''' FROM substring(raw_log FROM 'ACTION:\s*''([^\n]+)')), '([A-Z])', ' \1', 'g')) AS action_line,
  -- 提取AUDITDATA中的ClientIP
  'CLIENTIP: ' || (regexp_replace(substring(raw_log FROM 'AUDITDATA:\s*(\{.*\})'), '"', '"', 'g')::jsonb ->> 'ClientIP') AS clientip_line
FROM audit_records;

逻辑说明 #

• 正则匹配用substring函数的模式捕获功能，直接提取对应字段的原始值
• trim('''' FROM xxx)用于去除字段值两侧多余的单引号
• ACTION字段的regexp_replace逻辑为：为每个大写字母前插入空格，再trim掉开头的多余空格，实现驼峰命名转空格分隔格式
• AUDITDATA字段先通过正则提取JSON内容，替换所有HTML转义的"为标准双引号，转为jsonb类型后就可以用->>运算符直接提取对应属性值
• 邮箱首字母大写通过upper(left(xxx,1))处理首字符，剩余字符保持原格式输出
  如果不需要输出拼接好的键值对行，只需要获取字段值，去掉'xxx: ' ||前缀即可。

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