Dynamic JSON Parsing in Oracle using Keys from other table

Question

I need help in extracting data from JSON column stored in oracle table. JSON Data column can have maximum of 10 keys and can keys count can vary from 1-10. I have to create view in relational table format using column names stored in metadata table and create dynamic query that reads column data.

    CREATE TABLE json_species
(
   id     NUMBER NOT NULL PRIMARY KEY,
   info   CLOB CONSTRAINT is_json CHECK (info IS JSON ) )
/
 CREATE TABLE json_species_props
(
   Props   varchar(100)
   )
BEGIN
   INSERT INTO json_species
        VALUES (1, '{"name":"Spider"}');
 
   INSERT INTO json_species
        VALUES (2, '{"name":"Elephant", "trunk_length":"10"}');
 
   INSERT INTO json_species
        VALUES (3, '{"name":"Shark", "fin_count":"4"}');
        
   Insert into json_species_props values ('name')
   Insert into json_species_props values ('trunk_length')
   Insert into json_species_props values ('fincount')   
 
   COMMIT;
END;

I need output in below relational format

Name        Trunk_length fincount
Spider      NULL         NULL
Elephant    10           NULL
Shark       NULL         4

Please guide as I am new to JSON data extraction

can you provide the json ouput you expect ?

Roberto Hernandez
– Roberto Hernandez

2020-07-21 18:37:04 +00:00
Commented Jul 21, 2020 at 18:37 — Roberto Hernandez
– Roberto Hernandez, Commented Jul 21, 2020 at 18:37
@RobertoHernandez sorry, I have updated my query

NKapoor
– NKapoor

2020-07-21 19:20:58 +00:00
Commented Jul 21, 2020 at 19:20 — NKapoor
– NKapoor, Commented Jul 21, 2020 at 19:20

Chris Saxon · Accepted Answer · 2020-07-22 13:55:56Z

1

Assuming you're on 12.2 or higher, the easiest way is to use the JSON Data Guide. With this you can create a view that does the JSON -> relational conversion for you. No need for a separate properties table!

To this, first create a JSON search index:

create search index json_species_i 
  on json_species ( info )
  for json 
  parameters ( 'dataguide on' );

Then create the view:

exec dbms_json.create_view_on_path ( 'species', 'json_species', 'info', '$' );

select * from species;

ID    INFO$name    INFO$fin_count    INFO$trunk_length   
    1 Spider       <null>            <null>               
    2 Elephant     <null>            10                   
    3 Shark        4                 <null>

If you want more friendly names for the columns, you can use dbms_json.rename_column:

exec dbms_json.rename_column ( 'json_species', 'info', '$.name', dbms_json.type_string, 'NAME' );
exec dbms_json.create_view_on_path ( 'species', 'json_species', 'info', '$' );

select * from species;

ID    NAME        INFO$fin_count    INFO$trunk_length   
    1 Spider      <null>            <null>               
    2 Elephant    <null>            10                   
    3 Shark       4                 <null>

You can also use this to expose scalar attributes as virtual columns if you wish:

exec dbms_json.add_virtual_columns ( 'json_species', 'info' );

select * from json_species;

ID   INFO                                       NAME       INFO$fin_count   INFO$trunk_length   
   1 {"name":"Spider"}                          Spider     <null>           <null>               
   2 {"name":"Elephant", "trunk_length":"10"}   Elephant   <null>           10                   
   3 {"name":"Shark", "fin_count":"4"}          Shark      4                <null>

edited Jul 22, 2020 at 13:55

answered Jul 22, 2020 at 13:41

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

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

6 Comments

Chris Saxon Over a year ago

Ack, good catch; I missed the rename call. Thanks @APC

NKapoor Over a year ago

Thanks @ChrisSaxon but instead of creating on all existing as data, can it be driven from another table

Chris Saxon Over a year ago

Why? You can select the attributes you want from the view/table using this method. Sure, you can roll your own, but you'll have to use dynamic SQL - if using json_value or similar the path has to be a text literal.

NKapoor Over a year ago

My JSON column is of variable length, for some it can have 3 keys while for some it can have 10. I want to create view with 20 columns with column name (Key) coming from different metadata table. if new is added I will just update 11th column in metadata table and instead of 10 columns i will start getting 11th as well while other 9 wold remain NULL, if someone gets renamed I will simply add 12th and I will show prev and current value

Chris Saxon Over a year ago

If someone inserts JSON documents with a new attribute, all you have to do is call these procedures again - the database will re-create the view/virtual columns as needed

|

Collectives™ on Stack Overflow

Dynamic JSON Parsing in Oracle using Keys from other table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related