Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Praveen.

Asked: September 30, 2020 - 7:39 am UTC

Last updated: October 01, 2020 - 7:46 am UTC

Version: 12.1 & 19c

Viewed 1000+ times

You Asked

HI,
i have a json file, i have to insert into one table it's have multiple columns, How will convert into as record?
Please see the below example
Example:
{"EMPNO":123,"ENAME":"TEST","JOB":"ENGINEER","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}
{"EMPNO":3,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}
{"EMPNO":555,"ENAME":"TESTTTT","JOB":"SOFTWARE","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}
{"EMPNO":102,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}
{"EMPNO":444,"ENAME":"TESTTTT","JOB":"ENR2222","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}
{"EMPNO":700,"ENAME":"PANIPOORI","JOB":"CRICKET","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}
{"EMPNO":101,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}

i want to insert into emp table or any other ways to create insert scripts based on the Json file?

Thanks
Praveen

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_HT1U9Z9IZB03YZOD77B6D5411.html

and Chris said...

You can use JSON_table to convert JSON -> rows and columns, for example:

create table t (
  c1 varchar2(1000)
    check ( c1 is json )
);

insert into t 
with rws as (
  select '{"EMPNO":123,"ENAME":"TEST","JOB":"ENGINEER","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}' j from dual union all
  select '{"EMPNO":3,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}' j from dual union all
  select '{"EMPNO":555,"ENAME":"TESTTTT","JOB":"SOFTWARE","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}' j from dual union all
  select '{"EMPNO":102,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}' j from dual union all
  select '{"EMPNO":444,"ENAME":"TESTTTT","JOB":"ENR2222","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}' j from dual union all
  select '{"EMPNO":700,"ENAME":"PANIPOORI","JOB":"CRICKET","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}' j from dual union all
  select '{"EMPNO":101,"ENAME":"harish","JOB":"developer","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}' j from dual 
)
  select * 
  from   rws;
  
commit;

select j.* from t, json_table (
  c1, '$'
  columns (
    empno int path '$.EMPNO',
    ename varchar2(20) path '$.ENAME'
  )
) j;

EMPNO    ENAME       
     123 TEST         
       3 harish       
     555 TESTTTT      
     102 harish       
     444 TESTTTT      
     700 PANIPOORI    
     101 harish


From there just insert the results of the query to the target table.

You need to know the attribute names to do this though. If you want something that pulls out the attributes for you, from 12.2 you can use the JSON Data Guide.

To use this, add a JSON search index, use DBMS_JSON to create the view, and viola:

create search index ji
  on t ( c1 )
  for json 
  parameters ( 'dataguide on' );
  
exec dbms_json.create_view_on_path ( 'emp_v', 't', 'c1', '$' );

select * from emp_v;

C1$JOB       C1$MGR    C1$SAL    C1$COMM    C1$EMPNO    C1$ENAME     C1$DEPTNO    C1$HIREDATE   
ENGINEER     <null>    <null>    <null>             123 TEST         <null>       <null>         
developer    <null>    <null>    <null>               3 harish       <null>       <null>         
SOFTWARE     <null>    <null>    <null>             555 TESTTTT      <null>       <null>         
developer    <null>    <null>    <null>             102 harish       <null>       <null>         
ENR2222      <null>    <null>    <null>             444 TESTTTT      <null>       <null>         
CRICKET      <null>    <null>    <null>             700 PANIPOORI    <null>       <null>         
developer    <null>    <null>    <null>             101 harish       <null>       <null>

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 01, 2020 - 12:43 am UTC

Thank you Chris ☺️,
The solution is look good, is there any other way to insert or create insert script directly without inserting into Stage table???
Connor McDonald
October 01, 2020 - 12:56 am UTC

There isn't a stage table here, just a view, but you don't *have* to use that. If you *know* what the JSON structure is in advance, you just do insert-select

If you wanted to just generate insert statements, you could the "insert" hint on SQLcl or SQL Developer,

Need clarification

Praveen, October 01, 2020 - 12:53 am UTC

Chris, you have arrived the json data like SQL see the below:
In my case I have N number of JSON records, how can I convert??
 select '{"EMPNO":123,"ENAME":"TEST","JOB":"ENGINEER","MGR":null,"HIREDATE":null,"SAL":null,"COMM":null,"DEPTNO":null}' j from dual union all

Connor McDonald
October 01, 2020 - 1:10 am UTC

you need to elaborate on what you mean. give us a full test case

A reader, October 01, 2020 - 3:44 am UTC

Thanks, I have ST_LIFT_ACCESS_AUDITDTL table,
Every 30 minutes I'm receiving one JSON file from my thrid pary vendor it's has more than 1mb file size,
Now I have to insert into my base table it has multiple columns. Based on the column name I have to insert the records.

Please suggest what's the best way to do?? How to do??

Thanks for advance.
Chris Saxon
October 01, 2020 - 7:46 am UTC

You can select the document and pass it to JSON_table:

insert into ...
with rws as (
  select 'the json data' j from dual
)
  select * 
  from   rws, json_table (
    ...
  );


If you want more detailed help, please give a cut-down example of the JSON
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.