Oracle SQL如何根据指定Id更新表中JSON列对应Name字段值

适用场景：jsontable表jsoncol列存储JSON格式数据，需根据Company数组内对象的Id值匹配，更新对应对象的Name属性。

推荐写法（Oracle 12.2及以上版本支持） #

Oracle 12.2版本后提供了原生JSON_TRANSFORM函数，支持直接按JSON路径条件修改值，写法最简洁，性能最好。
示例需求：将Id=999的对象Name字段更新为DEF，对应SQL如下：

UPDATE jsontable
SET jsoncol = JSON_TRANSFORM(
    jsoncol,
    SET '$.Company[*]?(@.Id == 999).Name' = 'DEF'
)
WHERE JSON_EXISTS(jsoncol, '$.Company[*]?(@.Id == 999)');

使用时直接替换两个位置的参数即可：

• 把路径判断里的999替换为你需要匹配的目标Id值
• 把'DEF'替换为你要设置的新Name值

写法说明 #

• 路径$.Company[*]?(@.Id == 999).Name表示：定位到根节点下Company数组的所有元素，筛选出其中Id等于999的元素，取它的Name属性
• JSON_EXISTS条件用来过滤掉JSON里根本不包含目标Id的行，避免全表无效更新，减少性能损耗和行锁范围
• 如果Company数组里有多个对象的Id都等于目标值，该语句会把所有匹配对象的Name都更新为新值，符合需求
• 无论jsoncol是用CLOB/VARCHAR2存储的文本JSON，还是21c之后的原生JSON类型，该写法都兼容
• 如果你的Id字段是字符串类型而非数字，路径里的匹配值需要加双引号，例如'$.Company[*]?(@.Id == "999").Name'

低版本兼容写法（Oracle 12.1及更早版本） #

如果你的数据库版本不支持JSON_TRANSFORM，可以通过拆解JSON数组、修改后重组的方式实现，示例如下：

UPDATE jsontable jt
SET jsoncol = (
    SELECT JSON_OBJECT(
        KEY 'Company' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                KEY 'Info' VALUE JSON_OBJECT(KEY 'Address' VALUE c.Info.Address),
                KEY 'Name' VALUE CASE WHEN c.Id = 999 THEN 'DEF' ELSE c.Name END,
                KEY 'Id' VALUE c.Id
            )
        ) FORMAT JSON
    )
    FROM JSON_TABLE(jt.jsoncol, '$.Company[*]'
        COLUMNS (
            Id NUMBER PATH '$.Id',
            Name VARCHAR2(100) PATH '$.Name',
            Info VARCHAR2(200) FORMAT JSON PATH '$.Info'
        )
    ) c
)
WHERE JSON_EXISTS(jsoncol, '$.Company[*]?(@.Id == 999)');

该写法通过JSON_TABLE把数组拆成关系行，修改匹配行的Name值后再重新拼为原来结构的JSON，兼容性更好但性能比JSON_TRANSFORM差。

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