如何将Oracle表结构描述转换为指定格式的JSON？

以下是实现需求的最优查询语句，利用Oracle的JSON聚合函数完成结构转换：

SELECT
    JSON_OBJECT(
        'nombre' VALUE MAX(TABLE_NAME),
        'fields' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                'campo' VALUE COLUMN_NAME,
                'tipo' VALUE CASE DATA_TYPE
                    WHEN 'NVARCHAR2' THEN 'varchar'
                    WHEN 'NUMBER' THEN 'number'
                    WHEN 'TIMESTAMP' THEN 'timestamp'
                    -- 可根据业务需求扩展更多数据类型映射规则
                    ELSE DATA_TYPE
                END
            ) ORDER BY COLUMN_ID
        ) FORMAT JSON
    ) AS table_json
FROM
    ALL_TAB_COLUMNS
WHERE
    OWNER = 'PERSONAL'
    AND TABLE_NAME = 'CUSTOMER__C'
GROUP BY TABLE_NAME;

关键逻辑说明 #

1. GROUP BY TABLE_NAME：按表名聚合所有字段，确保最终生成单个包含完整字段数组的JSON对象，而非每条字段对应一个独立JSON。
2. JSON_OBJECT外层构造：生成包含nombre（表名）和fields（字段数组）的顶层JSON结构，用MAX(TABLE_NAME)获取分组后的唯一表名。
3. JSON_ARRAYAGG聚合字段：将每个字段的campo（列名）和tipo（转换后的数据类型）对象聚合成数组，ORDER BY COLUMN_ID保证字段顺序与表结构定义一致。
4. CASE DATA_TYPE类型映射：实现Oracle原生数据类型到目标类型的转换，可根据实际需求扩展分支。
5. FORMAT JSON：让输出的JSON自动带缩进，提升可读性。

预期输出示例 #

{
    "nombre": "CUSTOMER__C",
    "fields": [
        {
            "campo": "ID",
            "tipo": "varchar"
        },
        {
            "campo": "OWNERID",
            "tipo": "varchar"
        },
        {
            "campo": "ISDELETED",
            "tipo": "number"
        },
        {
            "campo": "NAME",
            "tipo": "varchar"
        }
    ]
}

额外提示 #

• 该查询要求Oracle版本为12c Release 2及以上（支持JSON_ARRAYAGG函数）。
• 若需要批量处理多个表，只需移除AND TABLE_NAME = 'CUSTOMER__C'条件，查询会为每个表生成独立的JSON结果。

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