How do I insert JSON data into an Oracle database?

Question

I am trying to insert this sample json data into an Oracle version 19 table. I tried this without any success:

DECLARE 
  myJSON  JSON_ARRAY_T;
  SET myJSON := 
    json_array_t ('
      [
        {"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 (CUST_NUM, SORT_ORDER, CATEGORY)
  SELECT CUST_NUM, SORT_ORDER, CATEGORY 
  FROM OPENJSON(myJSON)
  WITH (
   CUST_NUM int, 
   SORT_ORDER int, 
   CATEGORY VARCHAR2
  );
END;

Thank you for your time.

OPENJSON table function is for Microsoft SQL Server, not Oracle — Lukasz Szozda
– Lukasz Szozda, Commented Nov 5, 2020 at 18:28
The Oracle documentation contains an entire guide devoted to JSON Development. I suggest you read it rather than blindly applying syntax from other DBMS products. Find it here — APC
– APC, Commented Nov 5, 2020 at 18:32
Thank you for the link to the Oracle documentation. All I could find was how to store, retrieve and modify json data, whereas I am trying to use the json data to insert three records in the above example. — user2438439
– user2438439, Commented Nov 5, 2020 at 19:38

Chris Saxon · Accepted Answer · 2020-11-06 16:00:08Z

3

If you're trying to split the JSON document to rows-and-columns, the easiest way is to process it as a string and use json_table to extract out the relevant parts.

For example:

create table jt_test (
  CUST_NUM int, SORT_ORDER int, CATEGORY varchar2(100)
);

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

select * from jt_test;

CUST_NUM  SORT_ORDER CATEGORY    
   12345           1 ICE CREAM    
   12345           2 ICE CREAM    
   12345           3 ICE CREAM

answered Nov 6, 2020 at 16:00

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

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

2 Comments

henrry Over a year ago

Thanks You save me. I merge your solution with this:https://stackoverflow.com/a/68654608/12780274 And work for me.

henrry Over a year ago

this tutorial is for big json in python https://stackoverflow.com/a/73168799/12780274

Collectives™ on Stack Overflow

How do I insert JSON data into an Oracle database?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related