Oracle数据库循环插入JSON时JSON_TABLE路径索引替换问题

要实现先插入第一个item的所有bonuses，再插入第二个item的bonuses，你可以通过嵌套遍历item与对应bonuses或者动态索引拼接路径两种方式实现，具体如下：

方法一：嵌套遍历Item与Bonuses（推荐） #

这种方式先逐个解析每个item，再遍历该item下的bonuses数组，天然保证处理顺序：

DECLARE
    i_body CLOB := '{
    "items": [
        {
            "identificationMethod": "N",
            "idNumber": "7779",
            "bonuses": [
                {
                    "dateFrom": "2023-01-01",
                    "dateTo": "2023-12-31",
                    "value": 500,
                    "currency": "PLN",
                    "name": "11DOD",
                    "kind": "1"
                },
                {
                    "dateFrom": "2023-01-01",
                    "dateTo": "2023-12-31",
                    "value": 500,
                    "currency": "PLN",
                    "name": "22DOD",
                    "kind": "1"
                }
            ]
        },
        {
            "identificationMethod": "N",
            "idNumber": "7790",
            "bonuses": [
                {
                    "dateFrom": "2023-01-01",
                    "dateTo": "2023-12-31",
                    "value": 500,
                    "currency": "PLN",
                    "name": "333DOD",
                    "kind": "1"
                },
                {
                    "dateFrom": "2023-01-01",
                    "dateTo": "2023-12-31",
                    "value": 500,
                    "currency": "PLN",
                    "name": "444DOD",
                    "kind": "1"
                }
            ]
        }
    ]
}';
BEGIN
    -- 外层循环遍历每个item
    FOR item_rec IN (
        SELECT 
            id_number,
            bonuses_json
        FROM 
            json_table(
                i_body,
                '$.items[*]' COLUMNS(
                    id_number VARCHAR2(20) PATH '$.idNumber',
                    bonuses_json JSON PATH '$.bonuses'
                )
            )
    ) LOOP
        -- 内层循环遍历当前item下的所有bonuses
        FOR bonus_rec IN (
            SELECT
                date1 DATE PATH '$.dateFrom',
                date2 DATE PATH '$.dateTo',
                value1 NUMBER PATH '$.value',
                value2 VARCHAR2(10) PATH '$.currency',
                name VARCHAR2(20) PATH '$.name',
                kind VARCHAR2(10) PATH '$.kind'
            FROM
                json_table(
                    item_rec.bonuses_json,
                    '$[*]' COLUMNS(
                        date1 DATE PATH '$.dateFrom',
                        date2 DATE PATH '$.dateTo',
                        value1 NUMBER PATH '$.value',
                        value2 VARCHAR2(10) PATH '$.currency',
                        name VARCHAR2(20) PATH '$.name',
                        kind VARCHAR2(10) PATH '$.kind'
                    )
                )
        ) LOOP
            -- 执行插入操作，替换为你的表名和字段
            INSERT INTO your_target_table (
                date_from, date_to, bonus_value, currency, bonus_name, kind
            ) VALUES (
                bonus_rec.date1, bonus_rec.date2, bonus_rec.value1, 
                bonus_rec.value2, bonus_rec.name, bonus_rec.kind
            );
        END LOOP;
    END LOOP;
    COMMIT;
END;
/

方法二：动态索引拼接路径 #

如果你需要通过索引逐个处理item，可以先统计item数量，再循环索引拼接JSON路径：

DECLARE
    i_body CLOB := '你的JSON内容';
    v_item_count NUMBER;
BEGIN
    -- 获取items数组的长度
    SELECT COUNT(*) INTO v_item_count
    FROM json_table(i_body, '$.items[*]');

    -- 循环每个item的索引（Oracle JSON数组索引从0开始）
    FOR item_idx IN 0..v_item_count-1 LOOP
        -- 遍历当前索引item下的bonuses
        FOR bonus_rec IN (
            SELECT
                date1 DATE PATH '$.dateFrom',
                date2 DATE PATH '$.dateTo',
                value1 NUMBER PATH '$.value',
                value2 VARCHAR2(10) PATH '$.currency',
                name VARCHAR2(20) PATH '$.name',
                kind VARCHAR2(10) PATH '$.kind'
            FROM
                json_table(
                    i_body,
                    '$.items[' || item_idx || '].bonuses[*]' COLUMNS(
                        date1 DATE PATH '$.dateFrom',
                        date2 DATE PATH '$.dateTo',
                        value1 NUMBER PATH '$.value',
                        value2 VARCHAR2(10) PATH '$.currency',
                        name VARCHAR2(20) PATH '$.name',
                        kind VARCHAR2(10) PATH '$.kind'
                    )
                )
        ) LOOP
            -- 执行插入操作
            INSERT INTO your_target_table (
                date_from, date_to, bonus_value, currency, bonus_name, kind
            ) VALUES (
                bonus_rec.date1, bonus_rec.date2, bonus_rec.value1, 
                bonus_rec.value2, bonus_rec.name, bonus_rec.kind
            );
        END LOOP;
    END LOOP;
    COMMIT;
END;
/

注意事项 #

• Oracle JSON数组的索引是从0开始的，所以循环范围要从0到item数量-1；
• 动态拼接路径时，直接用字符串连接符||将索引变量嵌入路径即可，无需动态SQL；
• 推荐使用第一种嵌套遍历方式，代码更清晰，且无需额外统计数组长度。

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