Oracle更新查询：关联表匹配替换字段JSON结构中指定子串

适用前提 #

方案适配Oracle 12c及以上版本，优先使用原生JSON函数避免误伤JSON其他字段内容

推荐方案（Oracle 18c及以上） #

使用MERGE关联两张表匹配替换，通过原生JSON操作函数精准修改to字段值：

MERGE INTO table_a a
USING table_b b
ON (JSON_VALUE(a.description, '$.to' RETURNING VARCHAR2(20)) = b.col1)
WHEN MATCHED THEN
  UPDATE SET a.description = JSON_TRANSFORM(
    a.description,
    SET '$.to' = b.col2
  )
WHERE a.description IS JSON; -- 过滤非法JSON行避免报错

兼容方案（Oracle 12c版本） #

如果你的Oracle版本为12c，暂不支持JSON_TRANSFORM，且表A的JSON结构固定仅包含to和from两个键，可使用以下语句：

MERGE INTO table_a a
USING table_b b
ON (JSON_VALUE(a.description, '$.to' RETURNING VARCHAR2(20)) = b.col1)
WHEN MATCHED THEN
  UPDATE SET a.description = JSON_OBJECT(
    'to' VALUE b.col2,
    'from' VALUE JSON_VALUE(a.description, '$.from')
  )
WHERE a.description IS JSON;

事前校验建议 #

执行更新前先运行以下查询，确认匹配和替换结果符合预期，避免误操作：

SELECT 
  a.rownum,
  JSON_VALUE(a.description, '$.to') AS old_to_value,
  b.col1 AS matched_col1,
  b.col2 AS new_to_value,
  a.description AS old_json
FROM table_a a
JOIN table_b b ON JSON_VALUE(a.description, '$.to' RETURNING VARCHAR2(20)) = b.col1
WHERE a.description IS JSON;

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