更新oracle中JSON键的值

Question

我正在使用Oracle SQL Developer &希望在JSON中更新特定键的值。

我使用以下查询：

   update M  
  set  OBJECT = json_mergepatch (   
     OBJECT, '{ "$.ABC.DATA.WORKFLOW_STATUS.ABC" : "MONIKA" }' 
   ) WHERE KEY_1 = '121';

输出:错误报告

SQL错误: ORA-00904："JSON_MERGEPATCH"：无效标识符00904。00000 - "%s:无效标识符“

请给我一个正确的查询建议。

Answer 1

假设您要更新的JSON的结构类似于以下内容：

CREATE TABLE test_data
AS
    SELECT '121' AS key_1, '{
    "ABC": {
        "WORKFLOW_STATUS": {
            "ABC": "some value",
            "XYZ": 5.23
        },
        "OTHER": "fancy text"
    },
    "DEF": 123
}' AS object_val FROM DUAL
    UNION ALL
    SELECT '122' AS key_1, '{
    "ABC": {
        "WORKFLOW_STATUS": {
            "ABC": "other value",
            "XYZ": 2
        },
        "OTHER": "cool text"
    },
    "DEF": 999
}' AS object_val FROM DUAL;

更新JSON路径$.ABC.DATA.WORKFLOW_STATUS.ABC的正确语法如下所示：

UPDATE test_data
   SET object_val = json_mergepatch (object_val, '{"ABC":{"WORKFLOW_STATUS":{"ABC":"MONIKA"}}}')
 WHERE key_1 = '121';