JSON_TABLE, introduced in 12.2, "enables the creation of an inline relational view of JSON content. The JSON_TABLE operator uses a set of JSON path expressions to map content from a JSON document into columns in the view. Once the contents of the JSON document have been exposed as columns, all of the power of SQL can be brought to bear on the content of JSON document."
Step 2: First, we need to get the structure of the JSON response returned by apex_web_service.make_rest_request. I then formatted the JSON as below,
Formatted JSON: (Projecting Array Elements using NESTED)
[
{
"state": "Andaman and Nicobar Islands",
"statecode": "AN",
"districtData": [
{
"district": "Nicobars",
"notes": "",
"active": 0,
"confirmed": 0,
"deceased": 0,
"recovered": 0,
"delta": {
"confirmed": 0,
"deceased": 0,
"recovered": 0
}
},
{
"district": "North and Middle Andaman",
"notes": "",
"active": 0,
"confirmed": 1,
"deceased": 0,
"recovered": 1,
"delta": {
"confirmed": 0,
"deceased": 0,
"recovered": 0
}
},
{
"district": "South Andaman",
"notes": "",
"active": 0,
"confirmed": 32,
"deceased": 0,
"recovered": 32,
"delta": {
"confirmed": 0,
"deceased": 0,
"recovered": 0
}
}
]
},
{
"state": "Andhra Pradesh",
"statecode": "AP",
"districtData": [
{
"district": "Foreign Evacuees",
"notes": "",
"active": 111,
"confirmed": 111,
"deceased": 0,
"recovered": 0,
"delta": {
"confirmed": 0,
"deceased": 0,
"recovered": 0
}
},
{
"district": "Anantapur",
"notes": "",
"active": 40,
"confirmed": 136,
"deceased": 4,
"recovered": 92,
"delta": {
"confirmed": 0,
"deceased": 0,
"recovered": 0
}
},
{
"district": "Chittoor",
"notes": "",
"active": 110,
"confirmed": 208,
"deceased": 1,
"recovered": 97,
"delta": {
"confirmed": 0,
"deceased": 0,
"recovered": 0
}
},
{
"district": "East Godavari",
"notes": "",
"active": 15,
"confirmed": 59,
"deceased": 1,
"recovered": 43,
"delta": {
"confirmed": 0,
"deceased": 1,
"recovered": 0
}
}
]
}
]
Step 3: I then created the relational table and sequence.
CREATE TABLE fxgn_district_wise_india
(
batch_id NUMBER,
state VARCHAR2(100),
district VARCHAR2(100),
confirmed NUMBER,
lastupdatedtime VARCHAR2(100),
deltaconfirmed NUMBER,
updated_by VARCHAR2(100),
updated_on TIMESTAMP (6)
);
CREATE SEQUENCE fxgn_district_wise_india_s START WITH 1 INCREMENT BY 1;
Step 4: I then wrote an insert statement, which to be executed for each row.
INSERT
INTO fxgn_district_wise_india ( batch_id,
state,
district,
confirmed,
lastupdatedtime,
deltaconfirmed,
updated_by,
updated_on
)
SELECT fxgn_district_wise_india_s.nextval,
state,
district,
confirmed,
lastupdatedtime,
deltaconfirmed,
'KARKUVELRAJA.T',
localtimestamp
FROM dual,
JSON_TABLE (apex_web_service.make_rest_request( p_url => 'https://api.covid19india.org/v2/state_district_wise.json',
p_http_method => 'GET'), '$[*]'
COLUMNS ( state VARCHAR2(100) PATH '$.state',
NESTED PATH '$.districtData[*]'
COLUMNS (district VARCHAR2(100) PATH '$.district',
confirmed VARCHAR2(100) PATH '$.confirmed',
lastupdatedtime VARCHAR2(100) PATH '$.lastupdatedtime',
deltaconfirmed VARCHAR2(100) PATH '$.delta.confirmed')));
Note:
The "$.[*]" path says "Start at the top".
The "$.state" path says "Find the state column".
The "$.districtData[*]" path says "Find the districtdata array".
Each of the path clauses inside columns indicates the name-value pair to be used for that column. column names match the JSON key names.
Create a procedure, call the procedure whenever you need. it works!
CREATE OR REPLACE
PROCEDURE get_district_wise_india
AS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE fxgn_district_wise_india';
INSERT
INTO fxgn_district_wise_india
(
batch_id,
state,
district,
confirmed,
lastupdatedtime,
deltaconfirmed,
updated_by,
updated_on
)
SELECT fxgn_district_wise_india_s.nextval,
state,
district,
confirmed,
lastupdatedtime,
deltaconfirmed,
'karkuvelraja.t',
localtimestamp
FROM dual,
json_table (apex_web_service.make_rest_request( p_url => 'https://api.covid19india.org/v2/state_district_wise.json',
p_http_method => 'GET'), '$[*]'
COLUMNS ( state VARCHAR2(100) path '$.state',
NESTED path '$.districtData[*]'
COLUMNS (district VARCHAR2(100) path '$.district',
confirmed VARCHAR2(100) path '$.confirmed',
lastupdatedtime VARCHAR2(100) path '$.lastupdatedtime',
deltaconfirmed VARCHAR2(100) path '$.delta.confirmed')));
COMMIT;
exception
WHEN others THEN
raise_application_error (-20001, sqlerrm);
END get_district_wise_india;
/
Procedure Call:
BEGIN
get_district_wise_india();
END;
/
That's it. Happy APEXing!!!...
Reference:
- Oracle Docs: Using JSON_TABLE with JSON Arrays
- Oracle Docs: Invoking a RESTful Style Web Service
Comments
Post a Comment