DB2 v11.5中使用JSON_TABLE无法提取JSON数组问题

问题分析 #

你遇到的SQL0104N错误，核心原因是SYSIBM.JSON_TABLE的嵌套语法使用错误，或是你的DB2版本不支持NESTED PATH语法（如低于11.5版本）。以下提供两种兼容不同版本的解决方案，均可实现将JSON数组展开为行，同时保留外层字段的需求。

方案一：两次JSON_TABLE关联（兼容低版本DB2） #

先解析外层非数组字段，将数组提取为CLOB类型，再通过CROSS JOIN调用JSON_TABLE展开数组，确保每行数组元素都能关联到外层字段：

SELECT 
    outer_data.string,
    outer_data.type,
    outer_data.name,
    array_items.type2,
    array_items.name2
FROM JSON_TABLE(
    -- 最终替换为你的表CLOB字段，例如：your_table.json_clob_column
    '{"string":"string","array":[{"type":"main","name":"name"},{"type":"othertype","name":"othername"}],"object":{"type":"objecttype","name":"objectname"}}' 
    FORMAT JSON,
    'strict $' COLUMNS (
        string VARCHAR(20) PATH 'strict $.string',
        type VARCHAR(20) PATH 'strict $.object.type',
        name VARCHAR(20) PATH 'strict $.object.name',
        -- 提取数组为CLOB，用于后续解析
        array_clob CLOB(10000) PATH 'strict $.array'
    ) ERROR ON ERROR
) AS outer_data
-- 关联展开数组，若需兼容空数组场景，可替换为LEFT JOIN
CROSS JOIN JSON_TABLE(
    outer_data.array_clob 
    FORMAT JSON,
    'strict $[*]' COLUMNS (
        type2 VARCHAR(20) PATH 'strict $.type',
        name2 VARCHAR(20) PATH 'strict $.name'
    ) ERROR ON ERROR
) AS array_items;

方案二：使用NESTED PATH语法（DB2 11.5+版本支持） #

如果你的DB2版本为11.5或更高，可直接使用NESTED PATH子句实现嵌套数组展开，语法更简洁：

SELECT t.*
FROM JSON_TABLE(
    '{"string":"string","array":[{"type":"main","name":"name"},{"type":"othertype","name":"othername"}],"object":{"type":"objecttype","name":"objectname"}}' 
    FORMAT JSON,
    'strict $' COLUMNS (
        string VARCHAR(20) PATH 'strict $.string',
        type VARCHAR(20) PATH 'strict $.object.type',
        name VARCHAR(20) PATH 'strict $.object.name',
        -- 正确的NESTED PATH语法，直接指定数组路径展开
        NESTED PATH 'strict $.array[*]' COLUMNS (
            type2 VARCHAR(20) PATH 'strict $.type',
            name2 VARCHAR(20) PATH 'strict $.name'
        )
    ) ERROR ON ERROR
) AS t;

结果验证 #

两种方案均可得到你期望的结果：

空数组兼容处理 #

若需兼容数组为空的场景：

• 方案一中将CROSS JOIN替换为LEFT JOIN
• 两种方案均可将ERROR ON ERROR改为NULL ON ERROR，空数组时数组相关字段会返回NULL

内容的提问来源于stack exchange，提问作者Gustavo A. Hernández Quesada