Hi All,
I have the following JSON string into a variable.
sample_json varchar2 (32767) := '{
"fundingrounds" : [
{
"date" : "01-FEB-2011",
"name" : "Seed",
"moneyraised" : "160000000",
"investors" : [ { "name" : "Jason Calacanis", "lead" : "yes" }, { "name" : "Brian Chesky", "lead" : "no" } ]
},
{
"date" : "01-DEC-2014",
"name" : "Series A",
"moneyraised" : "141000000",
"investors" : [ { "name" : "Lowercase Capital", "lead" : "yes" }, { "name" : "Naval Ravikant", "lead" : "no" } ]
}
]
}';
And following two tables:
CREATE TABLE funding_rounds (
funding_round_id NUMBER
GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER )
NOT NULL,
funding_round_date DATE NOT NULL,
funding_round_name VARCHAR2(50) NOT NULL,
money_raised NUMBER NOT NULL
)
LOGGING;
ALTER TABLE funding_rounds ADD CONSTRAINT funding_rounds_pk PRIMARY KEY ( funding_round_id );
CREATE TABLE funding_round_investors (
funding_round_investor_id NUMBER
GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER )
NOT NULL,
funding_round_id NUMBER NOT NULL,
investor_name VARCHAR2(50) NOT NULL,
lead_investor_yn CHAR(1) NOT NULL
)
LOGGING;
ALTER TABLE funding_round_investors ADD CONSTRAINT funding_round_investors_pk PRIMARY KEY ( funding_round_investor_id );
ALTER TABLE funding_round_investors
ADD CONSTRAINT funding_round_investors_fk01 FOREIGN KEY ( funding_round_id )
REFERENCES funding_rounds ( funding_round_id )
NOT DEFERRABLE;
Can we extract this data into respective tables using PLSQL code block? There are nested JSON elements for investors that needs to be extracted into the child table for respective parent row.
Any code examples will be greatly appreciated.
Many Thanks and
Kind Regards,
Bilal