RedShift解析字符串型JSON数组拆行报错如何解决？

核心思路 #

先通过子查询/CTE将字符串类型的inputs字段转换为SUPER类型，再对SUPER类型的JSON数组做拆分行操作，避开同查询层级字段引用限制。

可直接运行的查询语句 #

WITH parsed_data AS (
    SELECT 
        *,
        JSON_PARSE(inputs) AS inputs_super
    FROM source.table
    -- 可选：过滤无效JSON行，避免解析报错
    -- WHERE JSON_VALID(inputs) = TRUE
)
SELECT 
    c.*,
    d.desc,
    d.name,
    d.values
FROM parsed_data AS c, c.inputs_super AS d;

如果偏好子查询写法，也可以使用以下等价语句：

SELECT 
    c.*,
    d.desc,
    d.name,
    d.values
FROM (
    SELECT 
        *,
        JSON_PARSE(inputs) AS inputs_super
    FROM source.table
) AS c, c.inputs_super AS d;

之前报错的原因说明 #

• 报错navigation on column "inputs" is not allowed as it is not SUPER type：只有SUPER类型的JSON结构支持直接导航拆分，字符串类型的JSON需要先通过JSON_PARSE转换为SUPER类型。
• 报错function expression in FROM may not refer to other relations of same query level：RedShift不允许在FROM子句的关联逻辑中直接使用依赖同查询层级其他表字段的函数结果，因此必须先在子查询/CTE中完成类型转换，再做数组拆分。

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