Redshift物化视图解析Dynamodb数组字段urlDetails异常问题

你之前的查询返回NULL，是因为urlDetails.L是JSON数组结构，无法直接通过.链式访问内部的M对象。需要借助Redshift的JSON处理函数先展开数组，再提取字段，最后聚合为目标格式。

方法一：生成期望的JSON数组结果 #

执行以下SQL可以直接得到你想要的JSON数组格式：

SELECT 
  data.dynamodb."NewImage"."ID"."S"::varchar(255) AS ID,
  JSON_AGG(
    JSON_OBJECT(
      'type' VALUE element."M"."type"."S"::varchar(255),
      'url' VALUE element."M"."url"."S"::varchar(255)
    )
  ) AS urlDetails
FROM 
  view,
  UNNEST(JSON_PARSE(data.dynamodb."NewImage"."urlDetails"."L"::varchar)) AS element
GROUP BY 
  data.dynamodb."NewImage"."ID"."S"::varchar(255);

各部分作用说明：

• JSON_PARSE(...)：将DynamoDB返回的数组字符串转换为Redshift可处理的JSON数组类型
• UNNEST(...)：把JSON数组展开为多行，每行对应一个数组元素
• JSON_OBJECT(...)：将每个元素的type和url字段组装成单个JSON对象
• JSON_AGG(...)：将多行JSON对象重新聚合为一个完整的JSON数组
• GROUP BY：确保每个ID对应的数组被正确聚合，避免重复数据

方法二：展开数组为单行记录 #

如果需要单独查看每个数组元素的字段，可以使用以下SQL：

SELECT 
  data.dynamodb."NewImage"."ID"."S"::varchar(255) AS ID,
  element."M"."type"."S"::varchar(255) AS type,
  element."M"."url"."S"::varchar(255) AS url
FROM 
  view,
  UNNEST(JSON_PARSE(data.dynamodb."NewImage"."urlDetails"."L"::varchar)) AS element;

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