How to easily parse JSON files into Oracle Database tables: A quick guide

Contents

In this blog post, I’ll show you how to Implement a custom procedure that simplifies the process of parsing JSON objects into an Oracle database.

In my previous article, I described my first encounter with the new Oracle Database 23ai functionality called JSON-Relational Duality Views. This feature is particularly useful for parsing or creating JSON objects in your Oracle database. Today I will write a custom procedure to make JSON parsing even easier.

Solving the problem

In my opinion, writing a custom procedure if you’re dealing with small JSON objects with a straightforward hierarchy. However, if you use JSON frequently and have to maintain multiple user-defined types and duality views, it may be worth creating a “Swiss knife” procedure that will do the work for you.

The tables I will work on while writing this article are Project Milestones table examples from Quick SQL in APEX.

A typical project’s JSON representation will have a main JSON object with few attributes and arrays for milestones, attachments, links, and action items.

{
  "projects": [
    {
      "id": "1",
      "name": "Pharmacy project",
      "owner": "Tomas",
      "created_by": "TK",
      "links": [
        {
          "name": "Rachel Morris",
          "url": "http://datefjo.do/memwez"
        }
      ],
      "action_items": [
        {
          "project_id": "1",
          "action": "Action 2",
          "the_desc": "N/A",
          "owner": "N/A",
          "status": "completed"
        }
      ],
      "milestones": [
        {
          "project_id": "1",
          "name": "Milestone 4",
          "status": "completed",
          "owner": "N/A",
          "started_date": "01.02.2024",
          "closed_date": "01.04.2024"
        }
      ],
      "attachments": [
        {
          "project_id": "1",
          "contributed_by": "TK"
        }
      ]
    }
  ]
}

Creating the procedure

Before I start to code the procedure, I want to create criteria I aim to accomplish.

Procedure input will be just JSON
Parse hierarchical JSON representing multiple tables
Get and validate table and column names automatically
Insert the data into tables in hierarchical order

I created a diagram of the steps necessary to parse the JSON into tables. You can see it below.

JSON structure and validation

First, I use a JSON_DATAGUIDE function to get the structure of the input JSON. Then I run a few queries to check if the tables (arrays) exist in the database and insert all the information into a custom json_structure_to_process table.

The table will hold the structure for me, so I don’t have to query it multiple times from input JSON if needed.

Processing the structure and data

In the next step, I take the highest object in the hierarchy, which in my case, is a “projects” array.

I run a simple loop where I create a dynamic SQL query that generates an insert with select from a JSON_TABLE. The query for the projects table looks just like below.

insert into projects (id,name,owner,created_by) 
select * 
from json_table('input_json','$.projects[*]' 
    columns (
        id         VARCHAR2(11) PATH '$.id',
        name       VARCHAR2(26) PATH '$.name',
        owner      VARCHAR2(18) PATH '$.owner',
        created_by VARCHAR2(12) PATH '$.created_by'
    )
);

After executing this query, the loop will pick up on underlying arrays (tables) and create similar inserts for them.

In my case, the other arrays are Links, Action items, Milestones, and Attachments. The queries created by the procedure are below.

--Links
insert into links (url,name) 
select * 
from json_table('input_json','$.projects.links[*]' 
    columns (
        url  VARCHAR2(42) PATH '$.url',
        name VARCHAR2(26) PATH '$.name'
    )
);
--Action items
insert into action_items (owner,action,status,the_desc,project_id) 
select * 
from json_table('input_json','$.projects.action_items[*]' 
    columns (
        owner      VARCHAR2(14) PATH '$.owner',
        action     VARCHAR2(18) PATH '$.action',
        status     VARCHAR2(26) PATH '$.status',
        the_desc   VARCHAR2(14) PATH '$.the_desc',
        project_id VARCHAR2(11) PATH '$.project_id'
    )
);
--Milestones
insert into milestones (name,owner,status,project_id,closed_date,started_date)
select * 
from json_table('input_json','$.projects.milestones[*]' 
    columns (
        name         VARCHAR2(26) PATH '$.name',
        owner        VARCHAR2(14) PATH '$.owner',
        status       VARCHAR2(26) PATH '$.status',
        project_id   VARCHAR2(11) PATH '$.project_id',
        closed_date  DATE         PATH '$.closed_date',
        started_date DATE         PATH '$.started_date'
    )
);
--Attachments
insert into attachments (project_id,contributed_by) 
select * 
from json_table('input_json','$.projects.attachments[*]' 
    columns (
        project_id     VARCHAR2(11) PATH '$.project_id',
        contributed_by VARCHAR2(12) PATH '$.contributed_by'
    )
);

Most of my columns are VARCHAR2, but you can probably use any type of column because the type is automatically detected by JSON_DATAGUIDE. You can always tune your procedure to be more precise if you need a specific column type.

After I run through all the objects in JSON and all the data is inserted, the loop is done.

Cons and Complications

As you may have noticed, the biggest con of this solution is probably the requirement for array and column names to match the corresponding table and column names. However, you can always use a matching function that won’t demand a 100% accurate table or column name.

Another limitation is performance. Processing a big input JSON with a complicated hierarchy will take a lot of time.

Pros and advantages

The biggest advantage of using this custom procedure is the time saved while creating and maintaining the User-defined type or JSON duality views. This solution lets you drop a JSON into a procedure without any previous preparation or mapping.

So it depends on what time you value more: the time of the procedure that will parse the JSON or the time spent on development and maintenance.

Talk is cheap, show me the code!

All the code I used for this article is available here. The procedure is very simple, and I didn’t spend much time on it. If you decide to use it for your own needs, you might need to do some tuning here and there to tailor it to your specific requirements. Please let me know what you think about this solution! And thank you for reading my article. If you’re interested in this kind of content, check out my other publications on the Pretius blog: