In Part 1, we covered how to get a JSON response through an API and save the response to the database. Now let’s update our procedure to parse that data, save it to a new table to make querying and creating an APEX application easy.
Creating a New Table
Let’s start by creating a new table to hold the parsed data. Going through the JSON Response again, there are a few key fields that we need to pick out. We could save them all, but some of it may not be necessary. Here is that JSON response for your reference:
{
"Set": "SOR",
"Number": "010",
"Name": "Darth Vader",
"Subtitle": "Dark Lord of the Sith",
"Type": "Leader",
"Aspects": [
"Aggression",
"Villainy"
],
"Traits": [
"FORCE",
"IMPERIAL",
"SITH"
],
"Arenas": [
"Ground"
],
"Cost": "7",
"Power": "5",
"HP": "8",
"FrontText": "Action [{C=1}, {Exhaust}]: If you played a Villainy card this phase, deal 1 damage to a unit and 1 damage to a base.",
"EpicAction": "Epic Action: If you control 7 or more resources, deploy this leader. (Flip him, ready him, and move him to the ground arena.)",
"DoubleSided": true,
"BackText": "On Attack: You may deal 2 damage to a unit.",
"Rarity": "Special",
"Unique": true,
"Artist": "Borja Pindado",
"FrontArt": "https://d3alac64utt23z.cloudfront.net/images/cards/SOR/010.png",
"BackArt": "https://d3alac64utt23z.cloudfront.net/images/cards/SOR/010-b.png",
"VariantType": "Normal",
"MarketPrice": "4.30"
}
And here is the data and table structure for what I am interested in:
CREATE TABLE SWU_CARD_LIST
( CARD_NUMBER NUMBER,
CARD_SET VARCHAR2(40),
CARD_NAME VARCHAR2(4000),
TRAITS VARCHAR2(4000),
ARENAS VARCHAR2(4000),
CARD_COST NUMBER,
CARD_POWER NUMBER,
CARD_HP NUMBER,
FRONT_TEXT VARCHAR2(4000),
DOUBLE_SIDED VARCHAR2(4000),
RARITY VARCHAR2(4000),
IMAGE_URL VARCHAR2(4000),
BACK_IMAGE VARCHAR2(4000),
MARKET_PRICE NUMBER
);
Currently, the card number is used as the primary key, but we plan to change this to a unique identifier in a future release. Other than that, it is all very straightforward.
Creating a View for Querying JSON Data
I am going to do one more thing before getting the data into the table, and that is creating a view to query that JSON. We will use the view to get the data into the table:
CREATE OR REPLACE FORCE EDITIONABLE VIEW V_SWU_CARDS (
CARD_SET,
CARD_NUMBER,
CARD_NAME,
TRAITS,
ARENAS,
CARD_COST,
CARD_POWER,
CARD_HP,
FRONT_TEXT,
DOUBLE_SIDED,
RARITY,
FRONT_ART,
BACK_ART,
MARKET_PRICE
) AS
select X.card_set,
x.card_number,
x.card_name,
listagg(x.traits, ', ') within group(order by traits) traits,
listagg(x.arenas, ', ') within group(order by arenas) arenas,
x.card_cost,
x.CARD_POWER,
x.CARD_HP,
x.FRONT_TEXT,
x.DOUBLE_SIDED,
x.RARITY,
X.FRONT_ART,
X.BACK_ART,
X.MARKET_PRICE
From swu_card_names_json SCNJ,
JSON_TABLE(SCNJ.SCNJ_JSON,
'$'
columns(nested path '$.data[*]'
columns(CARD_SET varchar2(4000) path '$.Set',
CARD_NUMBER number path '$.Number',
CARD_NAME varchar2(4000) path '$.Name',
nested path '$.Traits[*]'
columns(TRAITS varchar2(4000) path '$'),
nested path '$.Arenas[*]'
columns(ARENAS varchar2(4000) path '$'),
CARD_COST number path '$.Cost',
CARD_POWER number path '$.Power',
CARD_HP number path '$.HP',
FRONT_TEXT varchar2(4000) path
'$.FrontText',
DOUBLE_SIDED varchar2(4000) path
'$.DoubleSided',
RARITY varchar2(4000) path '$.Rarity',
FRONT_ART VARCHAR2(4000) path '$.FrontArt',
BACK_ART VARCHAR2(4000) path '$.BackArt',
MARKET_PRICE NUMBER path '$.MarketPrice'))) X
group by X.card_set,
x.card_number,
x.card_name,
x.CARD_COST,
x.CARD_POWER,
x.CARD_HP,
x.FRONT_TEXT,
x.DOUBLE_SIDED,
x.RARITY,
x.front_art,
x.back_art,
x.market_price;
This may be a little confusing, but let’s break it down a little. Everything up until the JSON_TABLE is straightforward, but everything after that is a little more complicated. You can see that the JSON_TABLE is referencing the CLOB Column where we have the JSON stored. The main purpose of the JSON_TABLE is to create a row of relational data for each object inside a JSON array and output JSON values from within that object as individual SQL column values.
The ‘$’ tells us to look at the entire entry. In this case, we do not necessarily need this expression, but you will in many cases.
Updating the Procedure to Insert Data
Now we are going to go through each column that we need. The first line of the JSON has this entry:
"Set": "SOR",
We are going to pull that using this expression:
CARD_SET varchar2(4000) path '$.Set',
We are giving the value an alias, declaring its data type and the path is where that value is located, in this case “Set”. We use the path clause to delineate a portion of the row that you want to use as the column content.
One of the other parts of this query to take note of is the Nested Path portions. Those are for the arrays that are found within the JSON. So we have this array:
"Traits": [
"FORCE",
"IMPERIAL",
"SITH"
],
And the query will look at it like this:
nested path '$.Traits[*]'
columns(TRAITS varchar2(4000) path '$'),
Within our query, we are going to treat those arrays as a LISTAGG value. That will make it easier to read and remove any duplicates we would have if a card has more than one trait, which most do.
Here is how the data will look when we use the above query:
You can learn a lot more about JSON_TABLE here.
Now that we have the response, the new table, and the view let’s get the data in there. I am going to update the procedure that pulls the data from the API so that it adds it to the table immediately after we get it.
It will look something like this:
create or replace PROCEDURE get_swu_card_name_json as
l_response clob;
l_num number;
BEGIN
select max(SCNJ_ID) + 1 into l_num from swu_card_names_json;
l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(p_url => 'https://api.swu-db.com/cards/sor',
p_http_method => 'GET');
INSERT INTO swu_card_names_json VALUES (1, sysdate, l_response);
execute immediate 'truncate table swu_card_list';
for rec in (select * From v_swu_cards) loop
insert into swu_card_list
(card_number,
card_set,
card_name,
traits,
arenas,
card_cost,
card_power,
card_hp,
front_text,
double_sided,
rarity,
image_url,
back_image,
market_price)
values
(rec.card_number,
rec.card_set,
rec.card_name,
rec.traits,
rec.arenas,
rec.card_cost,
rec.card_power,
rec.card_hp,
rec.front_text,
rec.double_sided,
rec.rarity,
rec.front_art,
rec.back_art,
rec.market_price);
end loop;
end;
/
This will look through every entry in the JSON response and insert those values into the swu_card_list table.
Here is the data as it shows in the database after being pulled from the JSON:
Now that we’ve set up our new table, view, and procedure for pulling data from the API, we’re ready to move forward. In Part 3, we’ll focus on creating the APEX application to display our cards using their images. We’ll explore how to make our application visually appealing and functional, bringing the cards to life in a dynamic way. Stay tuned!
SUBMIT YOUR COMMENT