如何在Oracle SQL中将JSON行数据转换为列格式

问题描述 #

现有JSON文件包含两行独立的JSON对象：

{"backendAddr":"1.1.2.3:22","backendConnectTime":"HAHA"}
{"backendAddr":"3.3.5.6:22","backendConnectTime":"HEHE"}

需要将其转换为列格式输出，期望结果：

backendAddr   backendConnectTime
1.1.2.3:22    HAHA
3.3.5.6:22    HEHE

当前尝试的SQL仅能将每行JSON作为单列返回，未实现拆分：

SELECT *
      FROM JSON_TABLE (
              BFILENAME ('DIR_NAME',
                         'file.json'),
              '$[*]'
              COLUMNS (BIG_COL VARCHAR2 (4000 CHAR) FORMAT JSON PATH '$'))

解决方案 #

修改JSON_TABLE的COLUMNS子句，直接指定需要提取的字段及其JSON路径；同时由于文件中是多行独立JSON对象而非数组，需使用WITH UNCONDITIONAL WRAPPER将内容包装为JSON数组，以便$[*]路径遍历所有对象：

SELECT jt.backendAddr, jt.backendConnectTime
FROM JSON_TABLE(
    BFILENAME('DIR_NAME', 'file.json'),
    '$[*]' FORMAT JSON WITH UNCONDITIONAL WRAPPER
    COLUMNS (
        backendAddr VARCHAR2(50 CHAR) PATH '$.backendAddr',
        backendConnectTime VARCHAR2(50 CHAR) PATH '$.backendConnectTime'
    )
) jt;

关键说明 #

• BFILENAME('DIR_NAME', 'file.json')：指定JSON文件所在的Oracle目录对象和文件名，需确保目录已创建且当前用户拥有读取权限。
• WITH UNCONDITIONAL WRAPPER：将多行独立的JSON对象自动包装为一个JSON数组，解决非数组JSON无法通过$[*]遍历的问题。
• COLUMNS子句：定义要提取的列名、数据类型及对应JSON字段的路径（$.backendAddr和$.backendConnectTime）。

如果直接使用BFILENAME传入JSON_TABLE报错（部分Oracle版本可能不支持直接传入BFILE），可先将BFILE内容读取为CLOB再解析：

SELECT jt.backendAddr, jt.backendConnectTime
FROM (
    SELECT DBMS_LOB.GETCONTENTS(BFILENAME('DIR_NAME', 'file.json')) AS json_clob
    FROM DUAL
) t,
JSON_TABLE(
    t.json_clob,
    '$[*]' FORMAT JSON WITH UNCONDITIONAL WRAPPER
    COLUMNS (
        backendAddr VARCHAR2(50 CHAR) PATH '$.backendAddr',
        backendConnectTime VARCHAR2(50 CHAR) PATH '$.backendConnectTime'
    )
) jt;

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