如何在Oracle中更新JSON字段内TimeStamp属性的格式

以下是主流数据库对应的更新语句，用于将audit_info列里AddInfo和ChangeInfo下TimeStamp字段中秒后的冒号替换为小数点：

PostgreSQL #

UPDATE the_table
SET audit_info = jsonb_set(
    jsonb_set(
        audit_info::jsonb,
        '{AddInfo,TimeStamp}',
        to_json(regexp_replace((audit_info->'AddInfo'->>'TimeStamp')::text, '(\d{2}):(\d{7}Z)$', '\1.\2', 'g'))::jsonb
    ),
    '{ChangeInfo,TimeStamp}',
    to_json(regexp_replace((audit_info->'ChangeInfo'->>'TimeStamp')::text, '(\d{2}):(\d{7}Z)$', '\1.\2', 'g'))::jsonb
);

MySQL #

UPDATE the_table
SET audit_info = JSON_REPLACE(
    JSON_REPLACE(
        audit_info,
        '$.AddInfo.TimeStamp',
        REGEXP_REPLACE(JSON_UNQUOTE(JSON_EXTRACT(audit_info, '$.AddInfo.TimeStamp')), '(\\d{2}):(\\d{7}Z)$', '$1.$2')
    ),
    '$.ChangeInfo.TimeStamp',
    REGEXP_REPLACE(JSON_UNQUOTE(JSON_EXTRACT(audit_info, '$.ChangeInfo.TimeStamp')), '(\\d{2}):(\\d{7}Z)$', '$1.$2')
);

注意事项 #

• 执行前建议先备份数据，或添加WHERE条件过滤需要修改的记录，避免不必要的全表更新
• 正则表达式(\d{2}):(\d{7}Z)$匹配秒数后的冒号及后续7位数字加Z的格式，若你的时间戳小数位长度不同，可调整\d{7}中的数字

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