Oracle 12c动态多层嵌套JSON查询视图构建需求

Got it, let's solve this problem of flattening that dynamic nested JSON stored in an Oracle 12c CLOB column. The key here is handling the variable levels of listOfItems—since each row can have different depths, a recursive approach is the way to go.

Approach Overview #

We'll use a recursive CTE (Common Table Expression) to iterate through every level of the JSON hierarchy:

• Start with the top-level JSON data, extracting its core fields (id, version, number, type, itemName).
• Recursively process each listOfItems array from the previous level, pulling out the same fields for each nested item and continuing until there are no more nested arrays.
• Wrap this recursive logic into a view so you can easily query across all flattened items.

Full SQL for the View #

Assuming your table is named source_table and the CLOB column holding the JSON is json_clob, here's the code to create the view:

CREATE OR REPLACE VIEW flattened_json_items AS
WITH recursive_json (level_num, parent_id, id, version, number_val, type_val, item_name, nested_items) AS (
    -- Anchor member: process top-level JSON
    SELECT
        1 AS level_num,
        NULL AS parent_id, -- No parent for top-level items
        jt.id,
        jt.version,
        jt.number_val,
        jt.type_val,
        jt.item_name,
        jt.nested_items
    FROM source_table st
    OUTER APPLY JSON_TABLE(
        st.json_clob,
        '$'
        COLUMNS (
            id NUMBER PATH '$.id',
            version NUMBER PATH '$.version',
            number_val VARCHAR2(50) PATH '$.number',
            type_val VARCHAR2(10) PATH '$.type',
            item_name VARCHAR2(100) PATH '$.itemName',
            nested_items CLOB PATH '$.listOfItems'
        )
    ) jt
    UNION ALL
    -- Recursive member: process nested listOfItems
    SELECT
        rj.level_num + 1 AS level_num,
        rj.id AS parent_id, -- Track parent item ID for lineage
        jt.id,
        jt.version,
        jt.number_val,
        jt.type_val,
        jt.item_name,
        jt.nested_items
    FROM recursive_json rj
    OUTER APPLY JSON_TABLE(
        rj.nested_items,
        '$[*]' -- Iterate over each element in the listOfItems array
        COLUMNS (
            id NUMBER PATH '$.id',
            version NUMBER PATH '$.version',
            number_val VARCHAR2(50) PATH '$.number',
            type_val VARCHAR2(10) PATH '$.type',
            item_name VARCHAR2(100) PATH '$.itemName',
            nested_items CLOB PATH '$.listOfItems'
        )
    ) jt
    WHERE rj.nested_items IS NOT NULL -- Stop recursion when no more nested items
      AND JSON_EXISTS(rj.nested_items, '$[*]') -- Ensure the array has elements
)
SELECT
    level_num,
    parent_id,
    id,
    version,
    number_val,
    type_val,
    item_name
FROM recursive_json
WHERE id IS NOT NULL; -- Filter out any empty rows from empty arrays

Key Details Explained #

• Recursive CTE: The recursive_json CTE has two parts:
  • The anchor member pulls the top-level JSON fields and captures the listOfItems array as a CLOB for the next level.
  • The recursive member takes the nested array from the previous level, uses JSON_TABLE to unpack each element, and repeats until there are no more nested arrays.
• OUTER APPLY: Ensures we don't lose rows when a level has no listOfItems array.
• JSON_EXISTS: Checks that the nested array has elements before continuing recursion, preventing unnecessary iterations.
• Tracking Fields: Added level_num to show the depth of each item and parent_id to track which item is the parent of the current one—useful for debugging or lineage queries.

How to Use the View #

Once the view is created, you can query it like any regular table. For example:

• Find all items where type_val = 'N':

  SELECT * FROM flattened_json_items WHERE type_val = 'N';
  

• Check if a specific id exists anywhere in the nested hierarchy:

  SELECT COUNT(*) FROM flattened_json_items WHERE id = 5000;
  

• Get all items under a parent ID:

  SELECT * FROM flattened_json_items WHERE parent_id = 4000;
  

Notes for Oracle 12.1.0.2 #

Make sure your database has the JSON features enabled (they are by default in 12c). Also, if your JSON has varying field names or data types, adjust the JSON_TABLE column definitions (e.g., change VARCHAR2 lengths or data types) to match your actual data.

内容的提问来源于stack exchange，提问作者common