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

I deleted my answer. I thought at first glance that was a typo mistake. My bad. — Roberto Hernandez
– Roberto Hernandez, Commented Sep 8, 2021 at 7:50
Which version and patch level of 19c are you using? It appears that it just isn't recognising 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. — Alex Poole
– Alex Poole, Commented Sep 8, 2021 at 9:19
I can confirm, it does not work in 19.3.0.0.0 @AlexPoole — Marmite Bomber
– Marmite Bomber, Commented Sep 8, 2021 at 9:34
Right... 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. — Alex Poole
– Alex Poole, Commented Sep 8, 2021 at 9:38
@Sobhan - check my comments; 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. — Alex Poole
– Alex Poole, Commented Sep 8, 2021 at 10:27

Marmite Bomber · Accepted Answer · 2021-09-08 12:59:56Z

3

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

edited Sep 8, 2021 at 12:59

answered Sep 8, 2021 at 9:31

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

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

1 Comment

Sobhan Over a year ago

thanks for your help, it works fine and can resolve my issue, but I prefer to do it in one transaction, this way i must have 2 DB call per request and looks not efficient for me, right?? anyway If I can not find a solution, I will use and approve your solution

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related