Oracle 19中如何用json_transform替换任意层级的指定JSON值

你的json_transform语句未生效的核心原因是：Oracle 19c的json_transform不支持通过带过滤器的路径（如$..locationId?(@ == "000")）批量修改多个匹配节点——虽然该路径能被json_query识别并返回所有目标值，但REPLACE操作仅支持单个节点的修改，无法批量应用到所有匹配项。

以下是两种可靠的解决方案：

方案一：PL/SQL递归遍历替换（推荐，支持任意层级） #

通过JSON_OBJECT_T和JSON_ARRAY_T API递归遍历JSON的所有层级，精准定位并替换目标属性值，适合处理大体积JSON且兼容任意嵌套结构。

创建替换函数 #

CREATE OR REPLACE FUNCTION update_location_id(p_json CLOB) RETURN CLOB IS
  l_obj JSON_OBJECT_T;
  l_keys JSON_KEY_LIST;
  l_value JSON_ELEMENT_T;
  l_arr JSON_ARRAY_T;
BEGIN
  l_obj := JSON_OBJECT_T.parse(p_json);
  l_keys := l_obj.get_keys;
  
  -- 遍历当前层级所有键
  FOR i IN 1..l_keys.COUNT LOOP
    l_value := l_obj.get(l_keys(i));
    
    -- 递归处理嵌套对象
    IF l_value.is_object THEN
      l_obj.put(l_keys(i), JSON_OBJECT_T(update_location_id(l_value.to_clob)));
    -- 遍历处理数组中的对象元素
    ELSIF l_value.is_array THEN
      l_arr := TREAT(l_value AS JSON_ARRAY_T);
      FOR j IN 0..l_arr.get_size-1 LOOP
        IF l_arr.get(j).is_object THEN
          l_arr.put(j, JSON_OBJECT_T(update_location_id(l_arr.get(j).to_clob)));
        END IF;
      END LOOP;
      l_obj.put(l_keys(i), l_arr);
    -- 匹配到目标属性且值为000时替换
    ELSIF l_keys(i) = 'locationId' AND l_value.to_string() = '"000"' THEN
      l_obj.put('locationId', '111');
    END IF;
  END LOOP;
  
  RETURN l_obj.to_clob;
END;
/

使用函数修改JSON #

SELECT update_location_id('
{
  "a": {
    "locationId":"000"
  }, 
  "b": {
    "locationId":"111", 
    "x": {
      "locationId":"000"
    },
    "y": [
      {"locationId":"000"},
      {"name":"test", "locationId":"000"}
    ]
  }
') AS updated_json
FROM dual;

执行后会返回所有locationId为000的节点已替换为111的JSON。

方案二：SQL结合JSON_TABLE动态生成替换语句（适合简单场景） #

先通过json_table提取所有目标属性的完整路径，再动态生成json_transform语句逐个替换。这种方法无需PL/SQL，但仅适合嵌套层级不复杂的场景：

WITH json_paths AS (
  SELECT '$' || REPLACE(path, '.', '$.') AS json_path
  FROM json_table(
    '你的JSON内容',
    '$..locationId?(@ == "000")'
    COLUMNS path VARCHAR2(1000) FOR ORDINALITY PATH
  )
)
SELECT json_transform(
  '你的JSON内容',
  REPLACE (SELECT LISTAGG(json_path || ' = ''111''', ', ') WITHIN GROUP (ORDER BY json_path) FROM json_paths)
) AS updated_json
FROM dual;

注意：这种方法需要确保路径生成正确，且当JSON层级极深时，路径长度可能超出限制，因此优先推荐方案一。

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