如何在PLSQL中批量修改嵌套JSON里Mercedes的carstate值为false

前提说明 #

你的数据库需要是Oracle 12c及以上版本（支持原生JSON处理功能），且BLOB列存储的JSON需格式合法（你提供的示例JSON存在语法错误，实际数据需确保键名引号闭合，比如"othervalue": "true"而非"othervalue: "true"）。

方法一：使用JSON_TRANSFORM（Oracle 19c+推荐） #

JSON_TRANSFORM可直接对JSON进行针对性修改，语法简洁高效，适合批量更新：

UPDATE your_table t
SET t.your_blob_column = 
  UTL_RAW.CAST_TO_RAW(
    JSON_TRANSFORM(
      UTL_RAW.CAST_TO_VARCHAR2(t.your_blob_column),
      SET '$.carsmap[*]?(@.carname == "Mercedes").carstate' = 'false'
    )
  )
WHERE JSON_EXISTS(
  UTL_RAW.CAST_TO_VARCHAR2(t.your_blob_column),
  '$.carsmap[*]?(@.carname == "Mercedes")'
);

代码说明： #

• UTL_RAW.CAST_TO_VARCHAR2：将BLOB类型转换为字符串格式的JSON，便于函数处理
• JSON_TRANSFORM的SET子句：定位carsmap数组中所有carname为"Mercedes"的元素，修改其carstate值为"false"
• JSON_EXISTS：过滤出包含目标元素的行，避免无意义更新，提升性能
• UTL_RAW.CAST_TO_RAW：将修改后的JSON字符串转回BLOB类型，存回原列

方法二：兼容Oracle 12c版本的处理方式 #

如果数据库版本低于19c，可通过JSON_TABLE解析数组、重组JSON的方式实现：

MERGE INTO your_table t
USING (
  SELECT 
    t.rowid AS rid,
    JSON_OBJECT(
      'othervalue' VALUE j.othervalue,
      'othervalue1' VALUE j.othervalue1,
      'othervalue2' VALUE j.othervalue2,
      'carsmap' VALUE JSON_ARRAYAGG(
        CASE 
          WHEN car.carname = 'Mercedes' THEN JSON_OBJECT(
            'modificationdate' VALUE car.modificationdate,
            'carname' VALUE car.carname,
            'carstate' VALUE 'false'
          )
          ELSE JSON_OBJECT(
            'modificationdate' VALUE car.modificationdate,
            'carname' VALUE car.carname,
            'carstate' VALUE car.carstate
          )
        END
      )
    ) AS updated_json
  FROM your_table t,
       JSON_TABLE(
         UTL_RAW.CAST_TO_VARCHAR2(t.your_blob_column),
         '$' COLUMNS (
           othervalue VARCHAR2(10) PATH '$.othervalue',
           othervalue1 VARCHAR2(10) PATH '$.othervalue1',
           othervalue2 VARCHAR2(10) PATH '$.othervalue2',
           NESTED PATH '$.carsmap[*]' COLUMNS (
             modificationdate VARCHAR2(20) PATH '$.modificationdate',
             carname VARCHAR2(50) PATH '$.carname',
             carstate VARCHAR2(5) PATH '$.carstate'
           )
         ) j,
         JSON_TABLE(
           UTL_RAW.CAST_TO_VARCHAR2(t.your_blob_column),
           '$.carsmap[*]' COLUMNS (
             modificationdate VARCHAR2(20) PATH '$.modificationdate',
             carname VARCHAR2(50) PATH '$.carname',
             carstate VARCHAR2(5) PATH '$.carstate'
           )
         ) car
  GROUP BY t.rowid, j.othervalue, j.othervalue1, j.othervalue2
) src
ON (t.rowid = src.rid)
WHEN MATCHED THEN UPDATE SET
  t.your_blob_column = UTL_RAW.CAST_TO_RAW(src.updated_json);

代码说明： #

1. 通过JSON_TABLE解析原JSON的顶层字段和carsmap数组的每个元素
2. 用CASE语句判断并修改目标元素的carstate值
3. 用JSON_ARRAYAGG重组carsmap数组，再通过JSON_OBJECT重新构建完整的JSON对象
4. 借助MERGE语句将修改后的JSON写回原表的BLOB列

批量处理注意事项 #

• 若数据量极大，建议分批次更新（比如按ROWID范围拆分），避免长时间锁表和事务过大
• 执行前务必备份数据，或在测试环境验证逻辑正确性
• 确保BLOB列存储的JSON格式完全合法，否则JSON函数会抛出异常

内容的提问来源于stack exchange，提问作者Re De Pasquale