Oracle - json_mergepatch updating column to NULL-CodePudding

I have a table column with a JSON:

{ "name":"Tony","age":"20","sex":"male" }

When I try to add new data with a json_mergepatch:

UPDATE users SET user =
  json_mergepatch(user, '{"email":"[email protected]"}')
where name = 'Tony';

My column data becomes Null instead of { "name":"Tony","age":"20","sex":"male", "email":"[email protected]" }

see fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7cbe31629bb709d1bbe7056fc6710673 Fiddle works, but on my code it doesn't for some reason. My JSON is valid.

What am I doing wrong?

UPDATE

It seems that Oracle has a limit of 4000 characters for JSON even if the column is CLOB. When operating with json_mergepatch if the column has 4000 characters it makes it NULL!
Found this trough testing.

Is there a way to overcome this limitation?

CodePudding user response：

This works for me:

CREATE TABLE users (
  id    NUMBER,
  xuser  VARCHAR2(4000),
  CONSTRAINT users_is_json CHECK (xuser IS JSON)
);

INSERT INTO users (id, xuser) VALUES (1, '{ "name":"Tony","age":"20","sex":"male" }');

UPDATE users a 
SET    a.xuser = JSON_MERGEPATCH(a.xuser, '{"email":"[email protected]"}') 
WHERE  a.xuser.name = 'Tony';

SELECT * FROM users;

ID  XUSER
--  -----
1   
{
"name" : "Tony",
"age" : "20",
"sex" : "male",
"email" : "[email protected]"
}

Since "user" is an oracle reserved word and it should be avoided to use those, I named it "xuser". When I remove the alias "a" it errors out with ORA-00904: "XUSER"."NAME": invalid identifier

CodePudding user response：

Found solution here: Oracle Update - JSON Merge Patch with more than 4000 characters

UPDATE users SET user =
  json_mergepatch(user, '{"email":"[email protected]"}' RETURNING CLOB)
where name = 'Tony';