Skip to Main Content
  • Questions

  • Splitting and storing Json string from file to table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prasad.

Asked: September 15, 2017 - 3:49 pm UTC

Last updated: September 21, 2017 - 2:48 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Hi Team..Please help me on below scenario.

I have data in a file as below. It has two records Id and JsonString DELIMITED By "|

1001"|{"id":"1234","code":"test1","Version":v1,"time":"2017-08-31","rank":2,"device":"TEST1234TEST","response":"approve","notes":[{"user":"lory","time":"2017-09-07","note":"Test"}],"actions":[{"user":"dev","action":"investigate","time":"2017-09-07"}],"research":[{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"}],"level":"level2"},{"id":"5678","code":"test2","Version":v2,"time":"2017-07-31","rank":1,"device":"test234wtest56","response":"reject","notes":[{"user":"tom","time":"2017-08-07","note":"Test"}],"actions":[{"user":"pk","action":"approve","time":"2016-09-07"}],"research":[{"code":"yyy","Score":700,"research_action":"approve"},{"code":"ppp","Score":444,"research_action":"reject"},{"code":"zzz","Score":100,"research_action":"approve"},{"code":"zzz","Score":300,"research_action":"approve"}],"level":"level1"},{"id":"9876","code":"test3","Version":v5,"time":"2015-07-31","rank":6,"device":"junkdevice","response":"reject","notes":[{"user":"mary","time":"2017-09-07","note":"test23"}],"actions":[{"user":"romo","action":"suspet","time":"2016-09-07"}],"research":[{"code":"zzzz","Score":900,"research_action":"deney"},{"code":"xxx","Score":4545,"research_action":"approve"},{"code":"rrt","Score":300,"research_action":"approve"},{"code":"uuu","Score":340,"research_action":"approve"}],"level":"level4"}

I want to store the records in a table as below.

create table test0915(id number, response varchar2(4000));

Id response
===================================
100{"id":"1234","code":"test1","Version":v1,"time":"2017-08-31","rank":2,"device":"TEST1234TEST","response":"approve","notes":[{"user":"lory","time":"2017-09-07","note":"Test"}],"actions":[{"user":"dev","action":"investigate","time":"2017-09-07"}],"research":[{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"}],"level":"level2"}

1001{"id":"5678","code":"test2","Version":v2,"time":"2017-07-31","rank":1,"device":"test234wtest56","response":"reject","notes":[{"user":"tom","time":"2017-08-07","note":"Test"}],"actions":[{"user":"pk","action":"approve","time":"2016-09-07"}],"research":[{"code":"yyy","Score":700,"research_action":"approve"},{"code":"ppp","Score":444,"research_action":"reject"},{"code":"zzz","Score":100,"research_action":"approve"},{"code":"zzz","Score":300,"research_action":"approve"}],"level":"level1"}

1001{"id":"9876","code":"test3","Version":v5,"time":"2015-07-31","rank":6,"device":"junkdevice","response":"reject","notes":[{"user":"mary","time":"2017-09-07","note":"test23"}],"actions":[{"user":"romo","action":"suspet","time":"2016-09-07"}],"research":[{"code":"zzzz","Score":900,"research_action":"deney"},{"code":"xxx","Score":4545,"research_action":"approve"},{"code":"rrt","Score":300,"research_action":"approve"},{"code":"uuu","Score":340,"research_action":"approve"}],"level":"level4"}

and Connor said...

I'll assume you can load the data in via external table or SQL Loader (plenty of examples here on AskTOM of that), so here's the SQL that could follow that:


SQL> with t as
  2  (
  3  select '1001"|{"id":"1234","code":"test1","Version":v1,"time":"2017-08-31","rank":2,"device":"TEST1234TEST","response":"approve","n
user":"dev","action":"investigate","time":"2017-09-07"}],"research":[{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJ
,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"}],"level":"level2"},{"id":"5678","code":"test2","Ve
nse":"reject","notes":[{"user":"tom","time":"2017-08-07","note":"Test"}],"actions":[{"user":"pk","action":"approve","time":"2016-09-07"}
code":"ppp","Score":444,"research_action":"reject"},{"code":"zzz","Score":100,"research_action":"approve"},{"code":"zzz","Score":300,"re
","Version":v5,"time":"2015-07-31","rank":6,"device":"junkdevice","response":"reject","notes":[{"user":"mary","time":"2017-09-07","note"
-07"}],"research":[{"code":"zzzz","Score":900,"research_action":"deney"},{"code":"xxx","Score":4545,"research_action":"approve"},{"code"
40,"research_action":"approve"}],"level":"level4"}' c from dual
  4  )
  5  select
  6    substr(c,1,instr(c,'|')-2) id,
  7    case
  8      when instr(c,'{"id":',1,idx+1) > 0 then
  9        substr(c,instr(c,'{"id":',1,idx),instr(c,'{"id":',1,idx+1)-instr(c,'{"id":',1,idx))
 10      else
 11        substr(c,instr(c,'{"id":',1,idx))
 12      end str
 13  from t,
 14    lateral(select level idx from dual connect by level <= regexp_count(t.c,'{"id":'));

ID
----
STR
----------------------------------------------------------------------------------------------------------------------------------
1001
{"id":"1234","code":"test1","Version":v1,"time":"2017-08-31","rank":2,"device":"TEST1234TEST","response":"approve","notes":[{"user
":"lory","time":"2017-09-07","note":"Test"}],"actions":[{"user":"dev","action":"investigate","time":"2017-09-07"}],"research":[{"c
ode":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score
":100,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"}],"level":"level2"},

1001
{"id":"5678","code":"test2","Version":v2,"time":"2017-07-31","rank":1,"device":"test234wtest56","response":"reject","notes":[{"use
r":"tom","time":"2017-08-07","note":"Test"}],"actions":[{"user":"pk","action":"approve","time":"2016-09-07"}],"research":[{"code":
"yyy","Score":700,"research_action":"approve"},{"code":"ppp","Score":444,"research_action":"reject"},{"code":"zzz","Score":100,"re
search_action":"approve"},{"code":"zzz","Score":300,"research_action":"approve"}],"level":"level1"},

1001
{"id":"9876","code":"test3","Version":v5,"time":"2015-07-31","rank":6,"device":"junkdevice","response":"reject","notes":[{"user":"
mary","time":"2017-09-07","note":"test23"}],"actions":[{"user":"romo","action":"suspet","time":"2016-09-07"}],"research":[{"code":
"zzzz","Score":900,"research_action":"deney"},{"code":"xxx","Score":4545,"research_action":"approve"},{"code":"rrt","Score":300,"r
esearch_action":"approve"},{"code":"uuu","Score":340,"research_action":"approve"}],"level":"level4"}


3 rows selected.


Rating

  (2 ratings)

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

Comments

External Tables

Rajeshwaran, Jeyabal, September 18, 2017 - 6:12 am UTC

....
I'll assume you can load the data in via external table or SQL Loader (plenty of examples here on AskTOM of that),
....

Not sure if they are on 12.1 or 12.2.

If they are on 12.1 then SQL Loader doesn't support loading JSON documents ( http://tinyurl.com/yc34yp9p ) - external table is the only possible case in 12.1

with 12.2 SQL loader support loading JSON documents.

demo@ORA12C> create table t(doc clob check (doc is json) );

Table created.

demo@ORA12C> $ sqlldr demo/demo@ora12c log=d:\log.txt control=d:\ctl.txt

SQL*Loader: Release 12.2.0.1.0 - Production on Mon Sep 18 11:35:49 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1

Table T:
  1 Row successfully loaded.

Check the log file:
  d:\log.txt
for more information about the load.

demo@ORA12C> select * from t;

DOC
----------------------------------------
{

1 row selected.

demo@ORA12C> $type d:\ctl.txt
load data
infile *
truncate into table t
( doc )
begindata
{"department":"Sales"}

demo@ORA12C>


BTW the above WITH clause return only two rows, not three rows.

demo@ORA12C> with t as
  2     (
  3     select '1001"|{"id":"1234","code":"test1","Version":v1,"time":"2017-08-31","rank":2,"device":"TEST1234TEST","response":"approve","n
  4  user":"dev","action":"investigate","time":"2017-09-07"}],"research":[{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code":"GJ
  5  ,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"}],"level":"level2"},{"id":"5678","code":"test2","Ve
  6  nse":"reject","notes":[{"user":"tom","time":"2017-08-07","note":"Test"}],"actions":[{"user":"pk","action":"approve","time":"2016-09-07"}
  7  code":"ppp","Score":444,"research_action":"reject"},{"code":"zzz","Score":100,"research_action":"approve"},{"code":"zzz","Score":300,"re
  8  ","Version":v5,"time":"2015-07-31","rank":6,"device":"junkdevice","response":"reject","notes":[{"user":"mary","time":"2017-09-07","note"
  9  -07"}],"research":[{"code":"zzzz","Score":900,"research_action":"deney"},{"code":"xxx","Score":4545,"research_action":"approve"},{"code"
 10  40,"research_action":"approve"}],"level":"level4"}' c from dual
 11     )
 12     select
 13       substr(c,1,instr(c,'|')-2) id,
 14       case
 15         when instr(c,'{"id":',1,idx+1) > 0 then
 16           substr(c,instr(c,'{"id":',1,idx),instr(c,'{"id":',1,idx+1)-instr(c,'{"id":',1,idx))
 17         else
 18           substr(c,instr(c,'{"id":',1,idx))
 19         end str
 20     from t,
 21       lateral(select level idx from dual connect by level <= regexp_count(t.c,'{"id":'))
 22  /

ID
----
STR
-----------------------------------------------------------------------------------------------------------------------------------
1001
{"id":"1234","code":"test1","Version":v1,"time":"2017-08-31","rank":2,"device":"TEST1234TEST","response":"approve","n
user":"dev","action":"investigate","time":"2017-09-07"}],"research":[{"code":"GJJJ","Score":100,"research_action":"SUSPECT"},{"code
":"GJ
,"research_action":"SUSPECT"},{"code":"GJJJ","Score":100,"research_action":"SUSPECT"}],"level":"level2"},

1001
{"id":"5678","code":"test2","Ve
nse":"reject","notes":[{"user":"tom","time":"2017-08-07","note":"Test"}],"actions":[{"user":"pk","action":"approve","time":"2016-09
-07"}
code":"ppp","Score":444,"research_action":"reject"},{"code":"zzz","Score":100,"research_action":"approve"},{"code":"zzz","Score":30
0,"re
","Version":v5,"time":"2015-07-31","rank":6,"device":"junkdevice","response":"reject","notes":[{"user":"mary","time":"2017-09-07","
note"
-07"}],"research":[{"code":"zzzz","Score":900,"research_action":"deney"},{"code":"xxx","Score":4545,"research_action":"approve"},{"
code"
40,"research_action":"approve"}],"level":"level4"}


2 rows selected.

demo@ORA12C>

Connor McDonald
September 19, 2017 - 2:00 am UTC

Cut/paste fail on my part, I was testing it with different data.

"If they are on 12.1 then SQL Loader doesn't support loading JSON documents"

is not particularly relevant here - check their table definition

Table Definition

Rajeshwaran Jeyabal, September 19, 2017 - 6:23 am UTC

Could you please elaborate this? looking at the Table definition, unable to conclude the info. please help.

....
is not particularly relevant here - check their table definition
....

create table test0915(id number, response varchar2(4000));

Connor McDonald
September 21, 2017 - 2:48 am UTC

They are not sqlldr-ing data into a table with a JSON constraint on it.
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

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library