Oracle - json_mergepatch updating column to NULL

Question

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?

user5507535 · Accepted Answer · 2021-11-15 17:13:49Z

2

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';

answered Nov 15, 2021 at 17:13

user5507535

1,8385 gold badges29 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Koen Lostrie · Accepted Answer · 2021-11-15 15:35:05Z

0

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

answered Nov 15, 2021 at 15:35

Koen Lostrie

19.5k2 gold badges16 silver badges25 bronze badges

Collectives™ on Stack Overflow

Oracle - json_mergepatch updating column to NULL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related