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 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 - though json_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:

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