1

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.

APC
  • 144,005
  • 19
  • 170
  • 281
user2438439
  • 21
  • 1
  • 3
  • 2
    [`OPENJSON`](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15) table function is for Microsoft SQL Server, not Oracle – Lukasz Szozda Nov 05 '20 at 18:28
  • 1
    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](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/index.html) – APC Nov 05 '20 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 Nov 05 '20 at 19:38

1 Answers1

2

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
Chris Saxon
  • 9,105
  • 1
  • 26
  • 42
  • Thanks You save me. I merge your solution with this:`https://stackoverflow.com/a/68654608/12780274` And work for me. – henrry Jul 23 '22 at 18:46
  • this tutorial is for big json in python `https://stackoverflow.com/a/73168799/12780274` – henrry Jul 30 '22 at 10:46