在oracle中更新json值 [英] update json value in oracle

问题描述

我在表中有一列以json格式存储数据.

I have a column in the table which stores data in json format.

例如.列:

select jsoncol from table; 

-它给出了

{"y" : "4", "n" : "0" , "default" : "4"}

当我尝试

select json_value(jsoncol, '$.n') from table;

我得到的值是 0 .

如何将 n 的值更新为 2 ，

{"y" : "4", "n" : "2" , "default" : "4"}

推荐答案

您可以在PL/SQL中使用JSON_ELEMENT_T.parse和<json_obj>.PUT方法.

You could use JSON_ELEMENT_T.parse and <json_obj>.PUT method within PL/SQL.

设置

create table tjson ( jsoncol CLOB CONSTRAINT tjson_chk CHECK (jsoncol IS JSON) );

insert into tjson (jsoncol) VALUES ( '{"y" : "4", "n" : "0" , "default" : "4"}');

PL/SQL块

DECLARE
    v_jsoncol       tjson.jsoncol%TYPE;
    v_json_obj      json_object_t;
    v_new_jsoncol   tjson.jsoncol%TYPE;
BEGIN
    SELECT
        jsoncol
    INTO v_jsoncol
    FROM
        tjson; --use appropriate where clause

    v_json_obj := TREAT(json_element_t.parse(v_jsoncol) AS json_object_t);
    v_json_obj.put('n',2); --modifies the value
    v_new_jsoncol := v_json_obj.to_string; --converts to string

    UPDATE tjson
    SET
        jsoncol = v_new_jsoncol; --update ( use appropriate where clause)

END;
/

修改后的数据

select jsoncol FROM tjson;

JSONCOL                                                                         
--------------------------------------------------------------------------------
{"y":"4","n":2,"default":"4"}

编辑:

从 Oracle 19c 开始，您可以使用

Starting Oracle 19c, You may use json_mergepatch

select json_mergepatch (jsoncol, '{"n" : "2"}') as new_json from tjson;
NEW_JSON
{
"y" : "4",
"n" : "2",
"default" : "4"
}

实时SQL演示

这篇关于在oracle中更新json值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！