I have a table that keeps USER
data.
Here is my table structure:
CREATE TABLE "USERS"
(
"ID" NUMBER(16,0) PRIMARY KEY,
"USER_NAME" VARCHAR2(85) UNIQUE NOT NULL,
"IDENTIFICATION_TYPE" NUMBER(3,0) NOT NULL REFERENCES IDENTIFICATION_TYPES(ID),
"IDENTIFICATION_CODE" VARCHAR2(24) NOT NULL,
"TRADING_CODE" VARCHAR2(85) NULL,
"PASSWORD" VARCHAR2(48) NOT NULL,
"SALT" VARCHAR2(24) NOT NULL,
"FLAGS" NUMBER(3) NOT NULL,
"PROFILE" NCLOB NOT NULL CONSTRAINT profile_json CHECK ("PROFILE" IS JSON),
"SETTINGS" NCLOB NOT NULL CONSTRAINT settings_json CHECK ("SETTINGS" IS JSON),
UNIQUE(IDENTIFICATION_TYPE,IDENTIFICATION_CODE)
);
As you can see I have a Json column named SETTINGS
.
And the data that will be kept in this column looks like :
{
"lang" : "fa-IR",
"cols" : [],
"watch_lists" :
{
"list_1" : [5,6,7],
"list_2" : [8,9],
"list_3" :[1,2,3]
}
}
Now my application receives an updated list of watch_lists
that I want to replace with current one.
After some research at first I could write the following query using JSON_MERGEPATCH()
function:
UPDATE USERS
SET SETTINGS = JSON_MERGEPATCH(SETTINGS, '{ "watch_lists": { "liist_1": [4,5],"liist_2": [1,3,5] }}' returning clob pretty)
WHERE USER_NAME = 'admin'
But I found that JSON_MERGEPATCH()
will merge the updated list with current one, but I needed to replace it, then finally I understood that I need to use the JSON_TRANSFORM()
function in order to replace the list, so I wrote the following query:
UPDATE USERS
SET SETTINGS = JSON_TRANSFORM(SETTINGS,
SET '$.watch_lists' =
'{ "liist_1": [4,5],"liist_2": [1,3,5] }'
FORMAT JSON)
WHERE USER_NAME = 'admin'
But now it throws an exception :
SQL Error [1747] [42000]: ORA-01747: invalid user.table.column, table.column, or column specification
I could not find the reason of this error to resolve it.
Can anyone help me?
Any help will be appreciated!!
Best Answer
It appears that it just isn't recognizing the
json_transform()
function - but it isn't clear exactly when it was added. It isn't mentioned in the "what's new" sections - thoughjson_mergepatch()
is. It's touted as a new 21c feature but, like e.g. SQL macros, seems to have been back-ported to a later 19.x release. it seems to work in 19.11, for instance.It isn't helpful when they add things to the generic 19c docs without making it clear when it will work. They do for some things; so this seems to be a documentation bug really.
The function didn't originally exist in 19c; it was added later (some time after 19.3), apparently as a back-port of new 21c functionality, and was added to the documentation at the same time, presumably. But the docs don't state which versions it works in, which is unhelpful.
Anyway we can use
JSON_MERGEPATH()
with a two step approach:1-Reset the attribute.
2-set it to the new value.
like below: