如何在Oracle PL/SQL中提取单个RESULT的JSON字符串？

问题背景 #

我已在Oracle PL/SQL中实现JSON解析，但需从存储在CLOB列的API响应JSON里，提取每个RESULT对应的完整JSON字符串（每个RESULT等价于主表的一条记录）。不想使用INSTR、SUBSTR这类字符串操作方法，已知PowerShell中的实现方式，但客户仅允许使用PL/SQL。

API响应JSON结构 #

{"header1": "val_header1",
  "header2": "val_header2",
  "header3": "val_header3",
  "header4": "",
  "header5": "val_header5",
  "header6": ["val_header6"],
  "_embedded": {
    "results": [
      { "col1": "value1", "col2": "value2", "col3": "value3", "col4": "value4", "col5": "value5",
        "col6": [{"col6a": "", "col6b": "value6b"}],
        "col7a": {
          "col7a1": {"col7a1a": "value7a1a", "col7a1b": "value7a1b", "col7a1c": "value7a1c"},
          "col7b1": [
            {"col7b1a": "value7a1a-1", "col7b1b": "value7a1a-1", "col7b1c": "value7a1a-1"},
            {"col7b1a": "value7a1a-2", "col7b1b": "value7a1a-2", "col7b1c": "value7a1a-2"}
          ]
        },
        "_embedded": {}
      },
      { "col1": "value1", "col2": "value2", "col3": "value3", "col4": "value4", "col5": "value5",
        "col6": [{"col6a": "", "col6b": "value6b"}],
        "col7a": {
          "col7a1": {"col7a1a": "value7a1a", "col7a1b": "value7a1b", "col7a1c": "value7a1c"},
          "col7b1": [
            {"col7b1a": "value7a1a-1", "col7b1b": "value7a1a-1", "col7b1c": "value7a1a-1"},
            {"col7b1a": "value7a1a-2", "col7b1b": "value7a1a-2", "col7b1c": "value7a1a-2"}
          ]
        },
        "_embedded": {}
      },
      { "col1": "value1", "col2": "value2", "col3": "value3", "col4": "value4", "col5": "value5",
        "col6": [{"col6a": "", "col6b": "value6b"}],
        "col7a": {
          "col7a1": {"col7a1a": "value7a1a", "col7a1b": "value7a1b", "col7a1c": "value7a1c"},
          "col7b1": [
            {"col7b1a": "value7a1a-1", "col7b1b": "value7a1a-1", "col7b1c": "value7a1a-1"},
            {"col7b1a": "value7a1a-2", "col7b1b": "value7a1a-2", "col7b1c": "value7a1a-2"}
          ]
        },
        "_embedded": {}
      }
    ]
  }
}

PowerShell实现示例 #

# 请求API
$get_list = Invoke-WebRequest -Uri $get_url
# 转换为PowerShell对象
$json_object = $get_list | ConvertFrom-Json
# 遍历每个result
foreach ($json_obj_detail in $json_object._embedded.results)
{
    # 转换为压缩的JSON字符串
    $obj_det_json = $json_obj_detail | ConvertTo-Json -Depth 100 -Compress
}

期望得到的单个RESULT JSON字符串示例 #

{ "col1": "value1", "col2": "value2", "col3": "value3", "col4": "value4", "col5": "value5", "col6": [ { "col6a": "", "col6b": "value6b" } ], "col7a": { "col7a1": { "col7a1a": "value7a1a", "col7a1b": "value7a1b", "col7a1c": "value7a1c" }, "col7b1": [ { "col7b1a": "value7a1a-1", "col7b1b": "value7a1a-1", "col7b1c": "value7a1a-1" }, { "col7b1a": "value7a1a-2", "col7b1b": "value7a1a-2", "col7b1c": "value7a1a-2" } ], }, "_embedded": {} }

尝试过的SQL（未得到预期结果） #

SELECT JSON_QUERY(jsn.clob_json,'$._embedded.results')
FROM json_documents jsn

PL/SQL解决方案 #

方法1：SQL查询直接提取 #

通过JSON_TABLE将results数组拆分为单行记录，再用JSON_QUERY提取每个元素的完整JSON，支持生成压缩格式：

SELECT 
  -- 提取格式化的JSON字符串
  JSON_QUERY(
    jsn.clob_json, 
    '$._embedded.results[' || jt.idx || ']' 
    RETURNING CLOB
    WITH WRAPPER FALSE
  ) AS result_json,
  -- 提取压缩后的JSON字符串（对应PowerShell的-Compress）
  JSON_SERIALIZE(
    JSON_QUERY(jsn.clob_json, '$._embedded.results[' || jt.idx || ']'),
    COMPACT
    RETURNING CLOB
  ) AS compressed_result_json
FROM json_documents jsn,
     JSON_TABLE(
       jsn.clob_json,
       '$._embedded.results[*]'
       COLUMNS idx FOR ORDINALITY
     ) jt;

方法2：PL/SQL块遍历处理 #

如果需要在PL/SQL逻辑中逐个处理每个RESULT，使用Oracle原生JSON对象API（Oracle 12cR2及以上支持）：

DECLARE
  l_clob CLOB;
  l_root_obj JSON_OBJECT_T;
  l_results_arr JSON_ARRAY_T;
  l_result_obj JSON_OBJECT_T;
  l_result_json CLOB;
BEGIN
  -- 从表中获取目标CLOB格式JSON
  SELECT clob_json INTO l_clob FROM json_documents WHERE id = 1; -- 替换为实际筛选条件
  
  -- 解析根JSON对象
  l_root_obj := JSON_OBJECT_T(l_clob);
  -- 获取results数组
  l_results_arr := l_root_obj.get_Array('_embedded.results');
  
  -- 遍历数组中的每个RESULT元素
  FOR i IN 0 .. l_results_arr.get_size() - 1 LOOP
    l_result_obj := l_results_arr.get_Object(i);
    -- 转换为格式化JSON字符串
    l_result_json := l_result_obj.to_String();
    -- 如需压缩格式，替换为下面一行
    -- l_result_json := l_result_obj.to_String(JSON_OBJECT_T.COMPACT);
    
    -- 此处添加自定义处理逻辑，比如插入主表、输出等
    DBMS_OUTPUT.PUT_LINE(l_result_json);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('处理失败: ' || SQLERRM);
END;
/

说明 #

• 两种方案均依赖Oracle原生JSON解析能力，避免了手动字符串操作，稳定性更高。
• JSON_SERIALIZE(..., COMPACT)和to_String(JSON_OBJECT_T.COMPACT)均可生成压缩后的JSON，与PowerShell示例中的-Compress效果一致。
• 需确保Oracle版本为12cR2及以上，原生JSON API才完全支持上述功能。

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