Redshift中从Super类型列提取嵌套字段为独立列的方法

问题分析 #

你的zip列是Redshift的Super类型，存储的是包含单个字典的数组。之前使用JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(zip), 'zip4')无效的原因是：JSON_SERIALIZE会把Super数组序列化为带方括号[]的JSON数组字符串，而JSON_EXTRACT_PATH_TEXT只能从JSON对象中提取字段，无法直接处理数组结构。

解决方案 #

Redshift的Super类型支持原生的数组和对象访问语法，无需转成JSON字符串处理，直接通过数组下标定位元素，再访问字典字段即可。

场景1：数组固定只有一个字典元素

针对你提供的测试数据，直接取数组的第一个元素（下标从0开始），再提取对应字段：

with cte as(
select JSON_PARSE('[{"zip1":"07192","zip2":""}]') as zip
union all
select JSON_PARSE('[{"zip1":"09102","zip2":"53"}]') as zip
)
select 
  zip[0].zip1 as zip1,
  zip[0].zip2 as zip2
from cte;

场景2：数组包含多个字典元素

如果数组里可能有多个元素，需要用UNNEST展开数组，再提取每个元素的字段：

with cte as(
select JSON_PARSE('[{"zip1":"07192","zip2":""},{"zip1":"07193","zip2":"12"}]') as zip
union all
select JSON_PARSE('[{"zip1":"09102","zip2":"53"}]') as zip
)
select 
  z.zip1,
  z.zip2
from cte,
unnest(zip) as z;

为什么不推荐转JSON字符串处理 #

使用JSON_SERIALIZE+JSON_EXTRACT_PATH_TEXT的方式需要额外的序列化/反序列化操作，性能远低于Super类型的原生访问语法，而且处理逻辑更复杂，优先使用原生语法是最优选择。

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