Parsing dynamic JSON into Oracle table

Question

I have a Oracle 12c Database with a table like this

    (
      MSISDN        VARCHAR2(15 BYTE),
      DOCUMENT      VARCHAR2(15 BYTE),
      LOAD_DATE     DATE,
      LIST_NAME     VARCHAR2(40 BYTE),
      ATRIB         VARCHAR2(40 BYTE),
      VALUE         VARCHAR2(200 BYTE)
    )

In the other hand, I have this JSON who contain the data for that table

{
   "DigitalClient":{
      "documentNumber":"99999999-R",
      "documentType":"PASSPORT",
      "lastLineDate":123213213213,
      "lastClientDate":123213213213,
      "segment":"EMPRESA"
   },
   "ADSL":{
      "idOrder":216668542,
      "status":"COMPLETED",
      "orderType":"STANDARD",
      "documentNumber":"161893223R",
      "technologyAccess":"FTTVULA",
      "dUserLastModifiedDate":1571329345000,
      "type":"PERSON"
   }
}

The idea is to parse this info in the table in this way:

MSISDN       DOCUMENT       LOAD_DATE       LIST_NAME      ATRIB           VALUE
------       ---------      -----------     ------------   --------        -----
911231231    6745671A       05/12/19        DigitalClient  documentNumber  99999999R
911231231    6745671A       05/12/19        DigitalClient  documentType    PASSPORT
911231231    6745671A       05/12/19        ADSL           idOrder         216668542
...

The three first fields are obtained outside of the JSON file, the JSON file related fields are the last three. As you can see, the field LIST_NAME is filled with the first level name, and the ATRIB and VALUE fields are filled with the second level name and value

Now, the hard part. The JSON structure change every day. I don't know what the JSON file contains, neither first or second levels field names nor how many structures come. The only thing I know is that the file has only 2 levels deep: first for the name of a list of attributes, and second for the attributes and their values of each list.

Anyone knows a good way to achieve this? I tried with the solution showed here but is not what I'm looking for, because I must extract the LIST_NAME and ATRIB info using a SUBSTR in the first column, and is not very efficient for loading a large bunch of records.

Thanks in advance!

Check here, this might help: https://blogs.oracle.com/apex/tips-for-parsing-json-in-apex — Ergi Nushi, Dec 05 '19 at 16:52
Thanks for your suggestion, but first I'm going to try with native JSON package of Oracle 12c — David, Dec 10 '19 at 11:01

score 3 · Answer 1 · answered Dec 05 '19 at 16:56

Provided you're on 12.2, you can use the JSON Data Guide to create views for you:

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

create search index ji 
  on t ( jdata )
  for json
  parameters ( 'sync (on commit)' );

insert into t values ('{
   "DigitalClient":{
      "documentNumber":"99999999-R",
      "documentType":"PASSPORT",
      "lastLineDate":123213213213,
      "lastClientDate":123213213213,
      "segment":"EMPRESA"
   },
   "ADSL":{
      "idOrder":216668542,
      "status":"COMPLETED",
      "orderType":"STANDARD",
      "documentNumber":"161893223R",
      "technologyAccess":"FTTVULA",
      "dUserLastModifiedDate":1571329345000,
      "type":"PERSON"
   }
}');

commit;

begin
  dbms_json.create_view_on_path ( 
    'vw', 't', 'jdata', '$'
  ); 
end;
/

select * from vw;

JDATA$type   JDATA$status   JDATA$idOrder   JDATA$orderType   JDATA$documentNumber   JDATA$technologyAccess   JDATA$dUserLastModifiedDate   JDATA$segment   JDATA$documentType   JDATA$lastLineDate   JDATA$documentNumber_1   JDATA$lastClientDate   
PERSON        COMPLETED              216668542 STANDARD           161893223R              FTTVULA                                    1571329345000 EMPRESA          PASSPORT                       123213213213 99999999-R                           123213213213

Note you need the is json constraint and the search index for this to work.

If the JSON contains an array, you'll get a row per element in the output.

For example:

insert into t values ( '{ "aDifferent" : ["array", "of", "stuff"] }' );
commit;

begin
  dbms_json.create_view_on_path ( 
    'vw', 't', 'jdata', '$'
  ); 
end;
/

select * from vw;

JDATA$type JDATA$status  JDATA$idOrder JDATA$orderType JDATA$documentNumber JDATA$technologyAccess  JDATA$dUserLastModifiedDate JDATA$segment JDATA$documentType JDATA$lastLineDate JDATA$documentNumber_1 JDATA$lastClientDate JDATA$string   
PERSON     COMPLETED         216668542 STANDARD        161893223R           FTTVULA                               1571329345000 EMPRESA       PASSPORT                 123213213213 99999999-R                     123213213213 <null>          
<null>     <null>               <null> <null>          <null>               <null>                                       <null> <null>        <null>                         <null> <null>                               <null> array           
<null>     <null>               <null> <null>          <null>               <null>                                       <null> <null>        <null>                         <null> <null>                               <null> of              
<null>     <null>               <null> <null>          <null>               <null>                                       <null> <null>        <null>                         <null> <null>                               <null> stuff

If the attribute names change in the JSON document, then you'll keep getting new columns in the view. So you may wish to drop this before creating it.

Hi, Chris, thanks for your answer. I'm trying your solution and when I tried to create the view, I encountered this errors (My DB is in spanish, sorry about it) ORA-06502: PL/SQL: error : error de conversión de carácter a número numérico o de valor ORA-06512: en "SYS.DBMS_JSON0", línea 24 ORA-06512: en "XDB.DBMS_JSON", línea 1000 ORA-06512: en línea 1 Also, I have a doubt, how do you suggest I can obtain the "ListName" info? The virtual columns name doesn't refer it:the JDATA$documentNumber can belong to DigitalClient or ADSL list, How can I know what of the 2 list is the right? THX! — David, Dec 10 '19 at 11:00
Not sure why you're getting the error - please post a new question showing how you arrived at this (create table + inserts + dbms_json call) — Chris Saxon, Dec 10 '19 at 14:08
If you know which object you want to get the attributes for, you can create a view on that path e.g. `dbms_json.create_view_on_path ( 'vw', 't', 'jdata', '$.ADSL' ); `. You can also inspect the structure with `select json_dataguide ( jdata ) from t;` — Chris Saxon, Dec 10 '19 at 14:10
I'm getting this error just following your example step by step in my local test DB. Maybe is a config issue? — David, Dec 11 '19 at 14:36
About your second comment, the problem is I don't know the object name, because the JSON isn't fix, it has a variable ammount of info and object-attributes structure. This is why I'm going crazy :-) — David, Dec 11 '19 at 14:38
Are you using 12.1 or 12.2? The JSON Data Guide is a 12.2 feature. Calling `json_dataguide` will tell you the names of all the attributes stored in the table. Which will give you a starting point at least. Though again, you need 12.2 — Chris Saxon, Dec 11 '19 at 16:47
Hmm, I don't know what the issue is, sorry! If you've used a straight copy-paste of my example, it sounds like there's some misconfiguration of your database — Chris Saxon, Dec 12 '19 at 13:11

Parsing dynamic JSON into Oracle table

1 Answers1