How do I Update Oracle database by JSON data with 1 query?

Question

I am trying to Update below sample json data into an Oracle version 19 table. (I want update 1000 rows from json with 1 query):

create table jt_test (
  CUST_NUM int, SORT_ORDER int, CATEGORY varchar2(100)
);
       
   [
            {"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"}
            {"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"}
            {"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
           ]

I use this tutorial and this for insert rows from json and that work perfect. But for update rows I have no idea. How can I do? Note: I use Oracle19C and connect and insert to db with cx_Oracle module python.

Code for Inserting by json to Oracle columns:

DECLARE 
  myJSON  varchar2(1000) := '[
  {"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"},
  {"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"},
  {"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
]';

BEGIN
  insert into jt_test
    select * from json_table ( myjson, '$[*]'
      columns ( 
        CUST_NUM, SORT_ORDER, CATEGORY
      )
    );
END;

Use [`merge`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F) statement with JSON table as a source to insert/update rows in a table. But you also can convert this JSON into dict at the Python side and use `executemany` with [bind variables](https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#binding-by-name-or-position) to insert this data via plain `insert ... values (...)` — astentx, Aug 05 '22 at 16:08

Tomy · Accepted Answer · 2022-08-25T11:59:53.123

In SQL Developer use below code :

MERGE INTO jt_test destttt  using(  
SELECT  CUST_NUM,SORT_ORDER,CATEGORY FROM json_table  (
'[
  {"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"},
  {"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"},
  {"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
]'
,'$[*]'
       COLUMNS  
      CUST_NUM int PATH '$.CUST_NUM ',
      SORT_ORDER int PATH '$.SORT_ORDER ',
     CATEGORY varchar2  PATH '$.CATEGORY ' ) )  srccccc


  ON ( destttt.CUST_NUM= srccccc.CUST_NUM)
WHEN MATCHED THEN UPDATE SET destttt.CATEGORY=srccccc.CATEGORY

WHEN NOT MATCHED THEN INSERT ( CUST_NUM,SORT_ORDER,CATEGORY) VALUES (srccccc.CUST_NUM,srccccc.SORT_ORDER,srccccc.CATEGORY);

In python with cx_Oracle use below code :

 long_json_string = '''[
    {"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "ICE CREAM"},
    {"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "ICE CREAM"},
    {"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "ICE CREAM"}
  ]'''

sql = '''
    DECLARE      jsonvalue  CLOB :=  :long_json_string     ;  
    begin  
     MERGE INTO jt_test destttt  using(  
        SELECT  CUST_NUM,SORT_ORDER,CATEGORY FROM json_table  (jsonvalue
         
        ,'$[*]'
               COLUMNS  
              CUST_NUM int PATH '$.CUST_NUM',
              SORT_ORDER int PATH '$.SORT_ORDER',
             CATEGORY varchar2  PATH '$.CATEGORY' ) )  srccccc
        
        
          ON ( destttt.CUST_NUM= srccccc.CUST_NUM)
        WHEN MATCHED THEN UPDATE SET destttt.CATEGORY=srccccc.CATEGORY
        
        WHEN NOT MATCHED THEN INSERT ( CUST_NUM,SORT_ORDER,CATEGORY) VALUES (srccccc.CUST_NUM,srccccc.SORT_ORDER,srccccc.CATEGORY);
                '''
cursor.execute(sql, long_json_string=long_json_string)

Note1: Do not forget in end use commit.

Note 2: Make sure that the column you use as a comparison is not repeated in a json and causes deadlock.

Note 3: there is case sensitivity json keys, that is, CUST_NUM is different from cust_num and CUST_num and ...

Wrong : CUST_NUM int PATH '$.CUST_num' or CUST_NUM int PATH '$.cusr _num'

Ok: CUST_NUM int PATH '$.CUST_NUM'

How do I Update Oracle database by JSON data with 1 query?

1 Answers1