Oracle PL/SQL动态获取JSON的data节点键名失败问题排查

可能的问题点及排查步骤 #

• 未递归处理嵌套JSON对象
  你的函数大概率只处理了顶层JSON键，没有对data这类嵌套JSON对象做递归遍历。如果data是JSON对象类型，需要主动检测其类型并触发递归遍历逻辑。
• 日志表插入逻辑未覆盖嵌套层级
  检查函数中写入日志表的代码是否仅在处理顶层键时执行，嵌套键的插入逻辑是否被遗漏。
• JSON类型判断逻辑错误
  确认判断data是否为JSON对象的逻辑是否正确，比如是否用is_object方法或IS OBJECT关键字准确识别类型，避免因判断错误跳过子键处理。
• 异常被静默吞掉
  如果函数中有异常处理但未记录错误，嵌套处理时的异常可能导致子键写入失败却没有任何提示。

修正后的函数示例 #

下面是加入递归处理嵌套JSON的示例函数，确保嵌套键能正常写入日志表：

CREATE OR REPLACE FUNCTION GET_EXPRESSION_VALUES(p_json CLOB, p_parent_key VARCHAR2 := NULL) RETURN VARCHAR2 IS
  l_json_obj JSON_OBJECT_T;
  l_keys JSON_KEY_LIST;
  l_key VARCHAR2(100);
  l_value CLOB;
BEGIN
  l_json_obj := JSON_OBJECT_T.parse(p_json);
  l_keys := l_json_obj.get_keys;

  FOR i IN 1..l_keys.COUNT LOOP
    l_key := l_keys(i);
    -- 根据值的类型获取对应内容
    IF l_json_obj.get(l_key).is_object THEN
      l_value := l_json_obj.get_object(l_key).to_clob;
    ELSE
      l_value := l_json_obj.get_string(l_key);
    END IF;

    -- 写入日志表，通过parent_key标记层级
    INSERT INTO json_key_log(key_name, parent_key, key_value)
    VALUES(l_key, p_parent_key, l_value);

    -- 递归处理嵌套JSON对象
    IF l_json_obj.get(l_key).is_object THEN
      GET_EXPRESSION_VALUES(l_value, l_key);
    END IF;
  END LOOP;

  RETURN 'SUCCESS';
EXCEPTION
  WHEN OTHERS THEN
    -- 记录异常便于排查
    INSERT INTO json_process_error(error_message, json_input, parent_key)
    VALUES(SQLERRM, p_json, p_parent_key);
    RAISE;
END;
/

额外注意事项 #

• 确保日志表（如json_key_log）包含parent_key字段，用于区分顶层键和data下的子键层级。
• 测试时可单独提取data节点的JSON传入函数，验证递归逻辑是否能正常获取子键并写入日志。
• 检查日志表的字段长度，避免因键名或值过长导致插入失败。

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