Mastering JSON in Oracle Database

Storing, querying, and creating JSON documents in Oracle Database can be a powerful way to manage and manipulate data. In this blog post, we'll walk through the steps to achieve this using a practical example.

Creating the Table

First, let's create a table to store our JSON documents. We'll use the following DDL to create a table named PTCG_CARDS:

CREATE TABLE "PTCG_CARDS" 
(
  "ID" VARCHAR2(20) NOT NULL ENABLE, 
  "CARD_JSON" VARCHAR2(4000) NOT NULL ENABLE, 
  CONSTRAINT "CHECK_JSON" CHECK (card_json IS JSON) ENABLE, 
  PRIMARY KEY ("ID")
  USING INDEX ENABLE
);

This table has two columns: ID and CARD_JSON. The CARD_JSON column is constrained to ensure that it contains valid JSON data.

Inserting JSON Data

Next, let's insert some JSON data into our table. Here's an example of the JSON data we'll be using:

{
  "id": "a1-036",
  "name": "CARD_NAME_36",
  "energy": "Fire",
  "type": "Pokemon",
  "subtype": "Stage 2",
  "hp": 180,
  "set": "A1",
  "pack": "Genetic Apex: Charizard",
  "rarity": "Double Rare",
  "weakness": "Water",
  "attacks": [
    {
      "name": "Slash",
      "effects": null,
      "damage": "60",
      "cost": [
        "Fire",
        "Colorless",
        "Colorless"
      ]
    },
    {
      "name": "Crimson Storm",
      "effects": "Discard 2 Fire Energy from this Pokémon.",
      "damage": "200",
      "cost": [
        "Fire",
        "Fire",
        "Colorless",
        "Colorless"
      ]
    }
  ],
  "abilities": null,
  "retreat": 2,
  "image": "https://limitlesstcg.nyc3.cdn.digitaloceanspaces.com/pocket/A1/A1_036_EN.webp"
}

To insert this JSON data into our table, we can use the following SQL statement:

INSERT INTO PTCG_CARDS VALUES ('a1-036', '{
  "id": "a1-036",
  "name": "Charizard ex",
  "energy": "Fire",
  "type": "Pokemon",
  "subtype": "Stage 2",
  "hp": 180,
  "set": "A1",
  "pack": "Charizard",
  "rarity": "Double Rare",
  "weakness": "Water",
  "attacks": [
    {
      "name": "Slash",
      "effects": null,
      "damage": "60",
      "cost": [
        "Fire",
        "Colorless",
        "Colorless"
      ]
    },
    {
      "name": "Crimson Storm",
      "effects": "Discard 2 Fire Energy from this Pok\u00e9mon.",
      "damage": "200",
      "cost": [
        "Fire",
        "Fire",
        "Colorless",
        "Colorless"
      ]
    }
  ],
  "abilities": [],
  "retreat": 2,
  "image": "https://limitlesstcg.nyc3.cdn.digitaloceanspaces.com/pocket/A1/A1_036_EN.webp"
}');

Querying JSON Data

Once we have our JSON data stored in the table, we can query it using SQL. For example, to retrieve the all the attributes of the card with id 'a1-036', we can use the following query:

SELECT 
    UPPER(id) as id, 
    JSON_VALUE(card_json, '$.name') AS name,
    lower(JSON_VALUE(card_json, '$.energy')) AS energy,
    JSON_VALUE(card_json, '$.type') AS type,
    JSON_VALUE(card_json, '$.subtype') AS subtype,
    JSON_VALUE(card_json, '$.hp') AS hp,
    JSON_VALUE(card_json, '$.set') AS set_,
    JSON_VALUE(card_json, '$.pack') AS pack,
    JSON_VALUE(card_json, '$.rarity') AS rarity,
    JSON_VALUE(card_json, '$.weakness') AS weakness,
    JSON_VALUE(card_json, '$.retreat') AS retreat,
    JSON_VALUE(card_json, '$.image') AS image
FROM PTCG_CARDS
WHERE id = 'a1-036'

This query uses the JSON_VALUE function to extract specific values from the JSON document.

Integrating with Oracle APEX

To integrate the above with Oracle APEX, follow these steps:

1. Run the DDL and Insert Commands:

   • Open Oracle APEX and navigate to the SQL Workshop.

   • Use the Script Editor to run the DDL for creating the table and the SQL statement for inserting the JSON data.

2. Create a Simple Web Application:

   • In Oracle APEX, create a new application.

   • Add a new page and select the "Cards" report type.

   • Configure the Cards report to display the JSON data from the PTCG_CARDS table.

   • Use the SQL query provided above to fetch and display the data in the Cards report.

By following these steps, you can effectively store, query, and create JSON documents in Oracle Database and integrate them into a web application using Oracle APEX. Happy coding! 🚀

Conclusion

Storing, querying, and creating JSON documents in Oracle Database is straightforward and powerful. By following the steps outlined in this post, you can effectively manage JSON data in your Oracle Database.