如何在Oracle中从同表另一列取值更新JSON字段？

需求说明 #

需要更新Oracle表中的JSON字段，原JSON格式如下：

{
    "common": [{
        "code": ["FIRST_CODE"]
    }]
}

要修改为：

{
    "common": [{
        "code": ["FIRST_CODE"],
        "type": 1
    }]
}

其中type的值来自同表的TYPE_ID列（该列值仅为1或2）。

原脚本问题分析 #

原PL/SQL脚本执行后未修改数据，核心问题如下：

1. 匹配条件失效：WHERE子句用LIKE匹配带HTML转义符"的完整JSON字符串，但表中存储的是实际双引号"，且JSON的空格、换行可能与脚本中的硬编码内容不一致，导致没有数据被选中进入循环。
2. 冗余查询：循环内再次查询TYPE_ID完全多余，游标已经获取了该行数据，直接调用rec.TYPE_ID即可。
3. 字符串替换不可靠：全字符串替换依赖完全一致的格式，稍有格式差异就会替换失败。

解决方案 #

方案1：修复字符串替换脚本 #

针对原脚本的问题调整，确保能匹配到数据并正确替换：

DECLARE
  v_my_json VARCHAR2(4000);
BEGIN
  FOR rec IN (
    SELECT t.ID, t.MY_JSON, t.TYPE_ID
    FROM database.table t
    -- 用JSON函数判断结构，不受格式影响
    WHERE JSON_VALUE(t.MY_JSON, '$.common[0].code[0]') = 'FIRST_CODE'
      AND JSON_EXISTS(t.MY_JSON, '$.common[0]')
      AND NOT JSON_EXISTS(t.MY_JSON, '$.common[0].type') -- 仅更新未包含type字段的行
  )
  LOOP
    -- 精准替换指定子串，避免全字符串匹配的问题
    v_my_json := REPLACE(rec.MY_JSON, '"code": ["FIRST_CODE"]', '"code": ["FIRST_CODE"], "type": ' || rec.TYPE_ID);
    
    UPDATE database.table
    SET MY_JSON = v_my_json
    WHERE ID = rec.ID;
  END LOOP;
  COMMIT; -- 必须提交事务才能保存修改
END;
/

方案2：使用Oracle原生JSON函数（推荐） #

Oracle提供了专门的JSON操作函数，无需循环，直接批量更新，且不受JSON格式差异影响：

UPDATE database.table t
SET MY_JSON = JSON_MODIFY(
    t.MY_JSON,
    '$.common[0].type',
    t.TYPE_ID,
    TRUE -- 当type字段不存在时自动添加
)
WHERE JSON_VALUE(t.MY_JSON, '$.common[0].code[0]') = 'FIRST_CODE'
  AND JSON_EXISTS(t.MY_JSON, '$.common[0]')
  AND NOT JSON_EXISTS(t.MY_JSON, '$.common[0].type');

COMMIT;

方案优势说明 #

• 原生JSON函数会解析JSON结构，无需担心空格、换行、缩进的差异，匹配更准确。
• 批量更新无需循环，执行效率更高。
• 避免字符串替换可能引发的误替换问题（比如JSON中存在相同子串但位置不同的情况）。

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