基于现有值更新JSONB类型字段中的出生日期格式

核心操作要点 #

• 访问jsonb中的出生日期值：用profile_fields ->> '2'获取键2对应的文本值（->>会将jsonb类型转为text，方便后续格式处理）
• 筛选待转换记录：通过正则表达式匹配yyyy-mm-dd格式的日期，避免重复处理已符合要求的数据
• 转换日期格式：先将字符串转成日期类型，再格式化目标格式dd-mm-yyyy
• 批量更新jsonb字段：借助jsonb_set函数替换原jsonb中的对应值

先验证转换结果（执行SELECT测试） #

SELECT 
    id,
    profile_fields ->> '2' AS original_dob,
    to_char(to_date(profile_fields ->> '2', 'YYYY-MM-DD'), 'DD-MM-YYYY') AS converted_dob
FROM your_table_name
WHERE profile_fields ->> '2' ~ '^\d{4}-\d{2}-\d{2}$';

批量UPDATE执行语句 #

BEGIN; -- 开启事务，出错可回滚，确保数据安全

UPDATE your_table_name
SET profile_fields = jsonb_set(
    profile_fields,
    '{2}', -- 指定要更新的jsonb键路径
    to_jsonb(to_char(to_date(profile_fields ->> '2', 'YYYY-MM-DD'), 'DD-MM-YYYY'))
)
WHERE profile_fields ->> '2' ~ '^\d{4}-\d{2}-\d{2}$'; -- 仅处理yyyy-mm-dd格式的记录

COMMIT; -- 确认转换结果正确后提交事务

关键说明 #

• 把语句中的your_table_name替换为你的实际表名
• jsonb_set的第三个参数需要是jsonb类型，所以用to_jsonb将格式化后的字符串转成对应类型
• WHERE子句的正则^\d{4}-\d{2}-\d{2}$精准匹配年-月-日格式，不会误操作已符合要求的dd-mm-yyyy记录
• 务必先执行SELECT测试语句，确认转换结果无误后再执行UPDATE

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