如何从PostgreSQL的context_data字段的复杂字符串中提取指定值

你给出的context_data存储内容实际为合法JSON数组结构，仅存在少量输入笔误，优先使用PostgreSQL原生JSON处理能力实现提取，性能和稳定性最优，具体实现如下： #

方案1：原生JSON解析（推荐） #

实现逻辑 #

1. 将字符串类型的context_data转换为jsonb类型
2. 展开JSON数组元素，过滤掉中间嵌套的JSON对象，仅保留+操作对应的三元组数组
3. 按需要的字段匹配，行转列得到目标结果

示例代码 #

SELECT
  '+' AS "action",
  MAX(CASE WHEN elem ->> 1 = 'id' THEN elem ->> 2 END)::BIGINT AS "id",
  MAX(CASE WHEN elem ->> 1 = 'first_name' THEN elem ->> 2 END) AS "first_name",
  MAX(CASE WHEN elem ->> 1 = 'name' THEN elem ->> 2 END) AS "last_name"
FROM 你的表名,
     jsonb_array_elements(context_data::JSONB) elem
WHERE 
  -- 过滤非数组类型的元素（即中间嵌套的JSON对象）
  jsonb_typeof(elem) = 'array'
  -- 仅保留新增(+)操作的条目
  AND elem ->> 0 = '+'
  -- 仅查询需要的字段，减少计算量
  AND elem ->> 1 IN ('id', 'first_name', 'name')
-- 如果需要批量处理多行数据，加上分组条件，按你的表主键分组即可
GROUP BY 你的表名.主键字段名;

方案2：正则匹配（备选，适合脏数据场景） #

如果存在部分context_data不符合JSON格式，可使用正则表达式直接匹配提取，写法更简单：

SELECT
  '+' AS "action",
  (regexp_match(context_data, '\["\+","id",(\d+)\]'))[1]::BIGINT AS "id",
  (regexp_match(context_data, '\["\+","first_name","([^"]+)"\]'))[1] AS "first_name",
  (regexp_match(context_data, '\["\+","name","([^"]+)"\]'))[1] AS "last_name"
FROM 你的表名;

方案对比 #

• JSON解析方案：性能更高，容错性更强，支持嵌套结构扩展，适合数据量较大、格式相对规范的场景
• 正则匹配方案：不需要处理JSON转换异常，适合存在少量非标准格式脏数据、查询字段固定的场景

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