Updating a Json column that contains a JsonArray using Json_transform() function in Oracle Database 19c Enterprise Edition

Question

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!!

Answer 1

It somehow seems that JSON_TRANSFORM is available starting with 21c version (and was backported in higher release of 19c).

Anyway you can use JSON_MERGEPATH with a two step approach.

Reset the attribute first and than set it to the new value:

UPDATE USERS 
SET SETTINGS = JSON_MERGEPATCH(SETTINGS,              
                   '{ "watch_lists": null}'
                  )
WHERE USER_NAME = 'admin'
;
UPDATE USERS 
SET SETTINGS = JSON_MERGEPATCH(SETTINGS,              
                   '{ "watch_lists": { "liist_1": [4,5],"liist_2": [1,3,5] }}'
                  )
WHERE USER_NAME = 'admin'
;