在Oracle SQL中更新JSON数据

Question

这是我的JSON数据中的一个oracle SQL列之一“ JSONCOL”的表中的一个名为“ JSontable”的表中，

{
  "Company": [
    {
      "Info": {
        "Address": "123"
      },
      "Name": "ABC",
      "Id": 999
    },
    {
      "Info": {
        "Address": "456"
      },
      "Name": "XYZ",
      "Id": 888
    }
  ]
}

我正在寻找 update> update 查询以更新具有基于新值的“名称”的所有值在特定的“ ID”值上。 提前致谢

Answer 1

在Oracle 19中，您可以使用 JSON_MERGEPATCH ：

UPDATE jsontable j
SET   jsoncol = JSON_MERGEPATCH(
                  jsoncol,
                  (
                    SELECT JSON_OBJECT(
                             KEY 'Company'
                             VALUE JSON_ARRAYAGG(
                                     CASE id
                                     WHEN 999
                                     THEN JSON_MERGEPATCH(
                                            json,
                                            '{"Name":"DEF"}'
                                          )
                                     ELSE json
                                     END
                                     FORMAT JSON
                                     RETURNING CLOB
                                   )
                             FORMAT JSON
                             RETURNING CLOB
                             )
                    FROM   jsontable jt
                           CROSS APPLY JSON_TABLE(
                             jt.jsoncol,
                             '$.Company[*]'
                             COLUMNS(
                               json VARCHAR2(4000) FORMAT JSON PATH '
对于示例数据：
CREATE TABLE jsontable (
  jsoncol CLOB CHECK (jsoncol IS JSON)
);

INSERT INTO jsontable (jsoncol)
VALUES ('{
  "Company": [
    {
      "Info": {
        "Address": "123"
      },
      "Name": "ABC",
      "Id": 999
    },
    {
      "Info": {
        "Address": "456"
      },
      "Name": "XYZ",
      "Id": 888
    }
  ]
}');

然后，在更新之后，该表包含：




 jsoncol 




 {“ company”：[{“ info”：{“ address”：“ 123”}，“ name”：“ def”：“ def”，“ id”：999}，{ “ info”：{“ address”：“ 456”}，“名称”：“ xyz”，“ id”：888}]} 




  db＆lt;＆gt; fiddle 在这里  
,
                               id   NUMBER PATH '$.Id'
                             )
                           )
                    WHERE  jt.ROWID = j.ROWID
                  )
                )

对于示例数据：

然后，在更新之后，该表包含：

jsoncol
{“ company”：[{“ info”：{“ address”：“ 123”}，“ name”：“ def”：“ def”，“ id”：999}，{ “ info”：{“ address”：“ 456”}，“名称”：“ xyz”，“ id”：888}]}

db＆lt;＆gt; fiddle 在这里

Answer 2

您可以在 replace（）中使用 json_table（）函数，以更新 name 的值（来自 abc to def ）对于特定的ID值（ 999 ），例如

UPDATE jsontable jt0
   SET jsoncol = ( SELECT REPLACE(jsoncol,jt.name,'DEF') 
                     FROM jsontable j,
                          JSON_TABLE(jsoncol,
                                    '
 19那个身份表和JSON值的值（ ID ）在整个表和每个单独的JSON值中都是唯一的。
  
 COLUMNS(NESTED PATH '$.Company[*]'
                                                COLUMNS(
                                                         name VARCHAR2 PATH '$.Name',
                                                         id   INT      PATH '$.Id' 
                                                        )
                                                )
                                     ) jt
                    WHERE jt.id = 999
                      AND j.id = jt0.id )

19那个身份表和JSON值的值（ ID ）在整个表和每个单独的JSON值中都是唯一的。