AWS Redshift Super类型嵌套数据查询Null异常求解

问题场景 #

处理Redshift Super类型嵌套数据时，存在字段导航异常：

• 执行查询获取上层对象时，明确可见e字段有值：

SELECT
    a.b.c[0]
FROM
    table

返回结果：

sample output 1
{"d":"Some String","e":"date and time"}    
sample output 2
{"d":"Some String","e":"date and time"}    

• 直接访问a.b.c[0].d能正常返回Some String，但访问a.b.c[0].e返回Null；即使将该Super对象存入新表后，qwer.e仍返回Null。

解决建议 #

1. 使用JSON解析函数替代直接字段导航

Redshift的Super类型直接字段导航在处理部分复杂嵌套或非标准数据类型时存在限制，改用JSON函数提取字段：

-- 方法1：用json_extract_path_text提取
SELECT json_extract_path_text(a.b.c[0]::varchar, 'e') AS e
FROM table;

-- 方法2：转为JSON对象后访问
SELECT json_parse(a.b.c[0]::varchar).e AS e
FROM table;

2. 转换为JSON字符串存储后查询

如果需要频繁访问该字段，可以将Super对象转为JSON字符串存储，避免Super类型的导航限制：

-- 创建存储JSON字符串的表
CREATE TABLE new_table (qwer_json varchar);
INSERT INTO new_table
SELECT a.b.c[0]::varchar
FROM table;

-- 查询时解析JSON字符串
SELECT json_parse(qwer_json).e AS e
FROM new_table;

3. 校验并转换字段数据格式

若e字段为日期时间类型，Redshift对Super类型中的日期格式兼容性有限，需确认格式是否匹配Redshift默认规则（如YYYY-MM-DD HH:MI:SS），不匹配时手动转换：

-- 根据实际日期格式调整TO_TIMESTAMP的格式参数
SELECT TO_TIMESTAMP(json_extract_path_text(a.b.c[0]::varchar, 'e'), 'YYYY-MM-DD HH:MI:SS') AS e
FROM table;

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