Oracle中嵌套数组JSON对象的Merge合并追加实现方案求助

需求背景 #

某Oracle数据表包含JSON列，原始JSON结构如下：

{ 
 "root":[
    {"MCR":"MCR_1",
     "MCR_COLUMNS":{ 
                      "MCR_COLUMN_1":"ABC1",
                      "MCR_COLUMN_2":"ABC2"
                   }
     },
    {"MCR":"MCR_2",
     "MCR_COLUMNS":{ 
                      "MCR_COLUMN_1":"XYZ1",
                      "MCR_COLUMN_2":"XYZ2"
                   }
     }
 ]
}               

需要编写MERGE语句处理两种场景：

场景1：更新已有MCR的列 #

若JSON中已存在指定MCR值，向对应MCR_COLUMNS对象追加新的键值对。例如追加以下内容：

{"MCR":"MCR_1",
 "MCR_COLUMNS":{ 
                  "MCR_COLUMN_3":"ABC3"
               }
 }

更新后JSON结构应为：

{ 
 "root":[
    {"MCR":"MCR_1",
     "MCR_COLUMNS":{ 
                      "MCR_COLUMN_1":"ABC1",
                      "MCR_COLUMN_2":"ABC2",
                      "MCR_COLUMN_3":"ABC3"
                   }
     },
    {"MCR":"MCR_2",
     "MCR_COLUMNS":{ 
                      "MCR_COLUMN_1":"XYZ1",
                      "MCR_COLUMN_2":"XYZ2"
                   }
     }
 ]
}               

场景2：新增MCR元素 #

若JSON中不存在指定MCR值，向root数组追加新的JSON对象。例如追加以下内容：

{"MCR":"MCR_3",
 "MCR_COLUMNS":{ 
                  "MCR_COLUMN_1":"UVW1",
                  "MCR_COLUMN_2":"UVW2"
               }
 }

更新后JSON结构应为：

{ 
 "root":[
    {"MCR":"MCR_1",
     "MCR_COLUMNS":{ 
                      "MCR_COLUMN_1":"ABC1",
                      "MCR_COLUMN_2":"ABC2"
                   }
     },
    {"MCR":"MCR_2",
     "MCR_COLUMNS":{ 
                      "MCR_COLUMN_1":"XYZ1",
                      "MCR_COLUMN_2":"XYZ2"
                   }
     },
    {"MCR":"MCR_3",
     "MCR_COLUMNS":{ 
                      "MCR_COLUMN_1":"UVW1",
                      "MCR_COLUMN_2":"UVW2"
                   }
     }
 ]
}               

已尝试方案及问题 #

尝试使用JSON_MERGEPATCH和JSON_TRANSFORM，但无法实现场景1的需求；由于无法预先判断MCR是否存在，单独针对场景2的方案不适用，需要能同时处理两种场景的MERGE语句。

解决方案 #

可以通过结合JSON_EXISTS判断MCR是否存在，在MERGE的UPDATE和INSERT分支分别处理两种场景：

假设数据表名为json_table，JSON列名为json_data，待追加的JSON数据存储在变量p_update_json中，具体MERGE语句如下：

MERGE INTO json_table t
USING (
    SELECT 
        JSON_VALUE(:p_update_json, '$.MCR') AS mcr_val,
        :p_update_json AS update_json
    FROM dual
) s
ON (JSON_EXISTS(t.json_data, '$.root[*]?(@.MCR == "' || s.mcr_val || '")'))
WHEN MATCHED THEN
    UPDATE SET t.json_data = JSON_TRANSFORM(
        t.json_data,
        APPEND '$.root[*]?(@.MCR == "' || s.mcr_val || '").MCR_COLUMNS' 
        VALUE JSON_VALUE(s.update_json, '$.MCR_COLUMNS', FORMAT JSON)
    )
WHEN NOT MATCHED THEN
    UPDATE SET t.json_data = JSON_TRANSFORM(
        t.json_data,
        APPEND '$.root' VALUE s.update_json
    );

说明 #

1. 匹配判断：通过JSON_EXISTS检查目标JSON的root数组中是否存在指定MCR值的元素。
2. 场景1处理（MATCHED分支）：使用JSON_TRANSFORM的APPEND操作，向匹配到的MCR元素的MCR_COLUMNS对象追加新的键值对。
3. 场景2处理（NOT MATCHED分支）：同样使用JSON_TRANSFORM的APPEND操作，向root数组追加整个新的JSON对象。

如果需要批量处理多条更新数据，可以将待更新数据放入临时表或子查询中，替换上述USING子查询的dual数据源。

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