Oracle 11g PL/SQL如何获取JSON数组指定位置的对象

由于Oracle 11g没有原生JSON解析函数，我们可以通过字符串处理的方式提取数组中指定位置的JSON对象，以下是具体实现方案：

实现思路 #

1. 先清理JSON字符串中的多余空格、换行符，避免干扰定位逻辑
2. 定位JSON数组的起始位置（跳过开头的[）
3. 遍历字符串，通过计数{和}的层级来识别完整对象，同时统计已找到的对象数量，匹配到目标位置时提取对应的字符串片段

PL/SQL函数示例 #

CREATE OR REPLACE FUNCTION get_json_array_element(
    p_json CLOB,
    p_position NUMBER -- 目标对象的位置，从1开始计数
) RETURN CLOB
IS
    v_start_idx NUMBER;
    v_end_idx NUMBER;
    v_bracket_count NUMBER := 0;
    v_object_count NUMBER := 0;
    v_char CHAR(1);
    v_clob_len NUMBER;
BEGIN
    -- 去除所有空格和换行，简化字符串处理
    p_json := REPLACE(REPLACE(p_json, CHR(32), ''), CHR(10), '');
    
    v_clob_len := DBMS_LOB.GETLENGTH(p_json);
    
    -- 定位数组起始点，跳过开头的[
    v_start_idx := INSTR(p_json, '[') + 1;
    
    FOR i IN v_start_idx..v_clob_len LOOP
        v_char := DBMS_LOB.SUBSTR(p_json, 1, i);
        
        IF v_char = '{' THEN
            v_bracket_count := v_bracket_count + 1;
            -- 标记新对象的起始
            IF v_bracket_count = 1 THEN
                v_object_count := v_object_count + 1;
                -- 如果是目标位置，记录对象起始索引
                IF v_object_count = p_position THEN
                    v_start_idx := i;
                END IF;
            END IF;
        ELSIF v_char = '}' THEN
            v_bracket_count := v_bracket_count - 1;
            -- 目标对象结束时，记录结束索引并退出循环
            IF v_bracket_count = 0 AND v_object_count = p_position THEN
                v_end_idx := i;
                EXIT;
            END IF;
        END IF;
    END LOOP;
    
    -- 提取字符串并还原换行格式，匹配预期输出样式
    RETURN '{' || CHR(10) || 
           REPLACE(DBMS_LOB.SUBSTR(p_json, v_end_idx - v_start_idx + 1, v_start_idx), ',', ',' || CHR(10)) || CHR(10) || '}';
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
/

使用示例 #

DECLARE
    v_json CLOB := '[
{
"id": 11,
"title": "1111",
},
{
"id": 22,
"title": "2222",
},
{
"id": 33,
"answer": "3333"
}]';
    v_result CLOB;
BEGIN
    -- 提取第2个对象
    v_result := get_json_array_element(v_json, 2);
    DBMS_OUTPUT.PUT_LINE(v_result);
END;
/

执行后输出结果：

{
"id":22,
"title":"2222",
}

注意事项 #

• 该函数针对无嵌套{}的简单JSON对象设计，若需处理嵌套结构，需调整层级计数逻辑
• 原始JSON中的末尾逗号（trailing comma）不符合标准JSON规范，但函数可正常处理

内容的提问来源于stack exchange，提问作者Fernando Erazo V