如何获取JSON元素结构？如何实现返回JSON结构的函数

Oracle、MySQL、PostgreSQL等主流数据库均未提供可直接接收JSON元素、返回嵌套结构描述的内置函数，你可以通过递归调用数据库自带的基础JSON解析函数，自定义实现目标函数f。

实现依赖的内置基础能力 #

自定义函数不需要从零写JSON解析逻辑，直接复用数据库原生JSON函数即可：

• JSON_TYPE：识别单个JSON元素的类型（对象、数组、数字、字符串、布尔、空值）
• JSON_KEYS：提取JSON对象下的所有一级键名
• 对应JSON类型的序列化/反序列化方法：将子节点的JSON内容取出，传入递归逻辑

自定义函数实现逻辑 #

函数核心是递归遍历JSON全量节点，规则完全匹配你需要的输出格式：

• 入参为合法JSON内容（支持JSON类型、CLOB/字符串类型存储的JSON串）
• 若当前节点是标量值：数字返回integer，字符串返回string，布尔值返回boolean，空值返回null
• 若当前节点是对象：遍历所有一级键，对每个键对应的值递归调用函数本身，拼接为键名 类型描述的格式，同层级键值对用逗号分隔
• 若当前节点是数组：业务场景中JSON数组通常存储同构数据，取第一个元素递归调用函数，结果外层包裹[]即可；如果需要兼容异构数组，可以遍历所有元素拼接多类型描述
• 所有层级递归拼接完成后，返回最终的结构字符串

Oracle 环境可直接运行的实现示例 #

CREATE OR REPLACE FUNCTION f(p_json IN CLOB) RETURN CLOB
IS
  v_type      VARCHAR2(20);
  v_obj       JSON_OBJECT_T;
  v_arr       JSON_ARRAY_T;
  v_keys      JSON_ARRAY_T;
  v_result    CLOB := '';
  v_child_json CLOB;
BEGIN
  v_type := JSON_TYPE(p_json FORMAT JSON);

  CASE v_type
    WHEN 'number' THEN RETURN 'integer';
    WHEN 'string' THEN RETURN 'string';
    WHEN 'boolean' THEN RETURN 'boolean';
    WHEN 'null' THEN RETURN 'null';
    WHEN 'array' THEN
      v_arr := JSON_ARRAY_T.parse(p_json);
      IF v_arr.get_size() = 0 THEN
        RETURN '[]';
      END IF;
      v_child_json := v_arr.get(0).to_string();
      RETURN '[' || f(v_child_json) || ']';
    WHEN 'object' THEN
      v_obj := JSON_OBJECT_T.parse(p_json);
      v_keys := v_obj.get_keys();
      FOR i IN 0 .. v_keys.get_size() - 1 LOOP
        IF i > 0 THEN
          v_result := v_result || ', ';
        END IF;
        v_child_json := v_obj.get(v_keys.get_string(i)).to_string();
        v_result := v_result || v_keys.get_string(i) || ' ' || f(v_child_json);
      END LOOP;
      RETURN v_result;
    ELSE RETURN 'unknown';
  END CASE;
EXCEPTION
  WHEN OTHERS THEN RETURN 'invalid json';
END f;
/

你给出的示例SQL存在少量语法错误，修正后调用测试：

SELECT f(
  json_array(
    json_object(
      'a' VALUE 1,
      'b' VALUE json_array(
        json_object('a' VALUE 1)
      )
    )
  )
) AS schema_desc FROM DUAL;

返回结果正好是你需要的[a integer, b [a integer]]。

如果是MySQL、PostgreSQL等其他数据库，递归逻辑完全一致，只需要把Oracle专属的JSON类型操作方法替换为对应数据库的原生JSON函数即可。

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud