NUHMAN.com

In Oracle SQL, dealing with JSON data that contains nested paths and arrays requires leveraging the power of JSON functions such as JSON_TABLE, JSON_VALUE, and JSON_QUERY. These functions allow you to efficiently extract and manipulate data stored in a JSON format.

If you need to split a nested JSON path and array (such as extracting courses from a department list), the JSON_TABLE function is especially useful, allowing you to transform JSON data into relational form.

1. Example Scenario

Let’s assume you have a table named my_table that stores a JSON string in a column called json_data. The structure of the JSON data might look like this:

json

{
  "college": {
    "name": "Example College",
    "departments": [
      {
        "name": "Computer Science",
        "courses": [
          {"name": "Algorithms", "code": "CS101"},
          {"name": "Data Structures", "code": "CS102"}
        ]
      },
      {
        "name": "Mathematics",
        "courses": [
          {"name": "Calculus", "code": "MATH101"},
          {"name": "Statistics", "code": "MATH102"}
        ]
      }
    ]
  }
}

Your objective might be to extract the department names and their associated courses, along with the course codes.

2. SQL Query Using JSON_TABLE

To extract the department names and courses with their respective course codes, you can use the JSON_TABLE function:

sql

SELECT 
    jt1.department_name,
    jt2.course_name,
    jt2.course_code
FROM 
    my_table,
    JSON_TABLE(
        json_data,
        '$.college.departments[*]'
        COLUMNS (
            department_name VARCHAR2(100) PATH '$.name',
            NESTED PATH '$.courses[*]' 
            COLUMNS (
                course_name VARCHAR2(100) PATH '$.name',
                course_code VARCHAR2(10) PATH '$.code'
            )
        )
    ) jt1,
    JSON_TABLE(
        jt1.courses,
        '$[*]'
        COLUMNS (
            course_name VARCHAR2(100) PATH '$.name',
            course_code VARCHAR2(10) PATH '$.code'
        )
    ) jt2;

Explanation:

• JSON_TABLE: This function is used to parse the JSON data and convert it into relational data.
• NESTED PATH: This is crucial for dealing with nested arrays within the JSON structure. In this case, it’s used to extract the list of courses within each department.
• jt1 and jt2: These are aliases for the two JSON_TABLE instances. The first instance (jt1) extracts department names and their nested array of courses, while the second instance (jt2) further splits this array to extract individual course details.

3. Adding Sequence Number for Courses

If you want to add a sequence number for each course within a department (for example, to number the courses), you can use the ROW_NUMBER() analytic function. Here’s how you modify the query to include a sequence number:

sql

SELECT 
    jt1.department_name,
    jt2.course_name,
    jt2.course_code,
    ROW_NUMBER() OVER (PARTITION BY jt1.department_name ORDER BY jt2.course_name) AS course_sequence
FROM 
    my_table,
    JSON_TABLE(
        json_data,
        '$.college.departments[*]'
        COLUMNS (
            department_name VARCHAR2(100) PATH '$.name',
            NESTED PATH '$.courses[*]' 
            COLUMNS (
                course_name VARCHAR2(100) PATH '$.name',
                course_code VARCHAR2(10) PATH '$.code'
            )
        )
    ) jt1,
    JSON_TABLE(
        jt1.courses,
        '$[*]'
        COLUMNS (
            course_name VARCHAR2(100) PATH '$.name',
            course_code VARCHAR2(10) PATH '$.code'
        )
    ) jt2;

Explanation:

• ROW_NUMBER(): This analytic function assigns a unique number to each course, starting from 1, for each department.
• PARTITION BY jt1.department_name: This ensures that the numbering restarts for each department.
• ORDER BY jt2.course_name: This orders the courses by name within each department to ensure consistent numbering.

Output Example:

DEPARTMENT_NAMECOURSE_NAMECOURSE_CODECOURSE_SEQUENCEComputer ScienceAlgorithmsCS1011Computer ScienceData StructuresCS1022MathematicsCalculusMATH1011MathematicsStatisticsMATH1022

4. Conclusion

Using Oracle SQL's JSON_TABLE function, you can efficiently parse and transform nested JSON data into a relational format. The NESTED PATH allows you to extract arrays within nested structures, while the ROW_NUMBER() function can help you add a sequence number to each entry within the array, which is useful for tasks like ordering or indexing.

This method gives you a powerful way to work with complex JSON data directly in SQL, without needing to manually parse or process the data externally.