Oracle JSON_TRANSFORM的insert/set on missing操作失效问题咨询

你的SQL执行无效的核心原因是JSON路径使用错误：

• 你写的$.contract[*].x里，[*]是JSON数组的通配符，用于匹配数组下的所有元素，但你的contract是一个单个JSON对象（不是数组），这个路径根本匹配不到任何节点，所以insert或者set on missing操作找不到目标位置，自然不会生效。
• 而更新现有字段的set操作能正常执行，是因为你指定的路径（比如$.contract.contract-type）是正确指向对象属性的，能匹配到对应节点。

正确写法 #

把路径里的[*]去掉，直接指定对象属性路径即可：

with j(jval) AS
(
select JSON_SERIALIZE('{"contract" :{"contract-type" : "P",
    "contract-id" : 123451,
    "proposal-no" : "123456",
    "serial-no" : "serialasd922",
    "product-code" : "s093",
    "contract-sign-dt" : "2016-12-01T00:00:00",
    "contract-start-dt" : "2016-12-09T00:00:00"
  }
}' pretty) from dual
)
SELECT   json_transform(jval, insert '$.contract.x' = '50' pretty)
FROM     j

或者用set on missing的写法也可以生效：

with j(jval) AS
(
select JSON_SERIALIZE('{"contract" :{"contract-type" : "P",
    "contract-id" : 123451,
    "proposal-no" : "123456",
    "serial-no" : "serialasd922",
    "product-code" : "s093",
    "contract-sign-dt" : "2016-12-01T00:00:00",
    "contract-start-dt" : "2016-12-09T00:00:00"
  }
}' pretty) from dual
)
SELECT   json_transform(jval, set '$.contract.x' = '50' on missing pretty)
FROM     j

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