如何使用Databricks SQL展开JSON列cdi_sentemailstatisticsdata

核心SQL语句 #

假设你的源表名为your_source_table，包含id（对应每条JSON的ID）和cdi_sentemailstatisticsdata（JSON格式字符串列），执行以下语句即可将list数组展开为目标行：

SELECT
  id,
  parsed_stats.lastUpdate,
  email_record.Count,
  email_record.EmailClient,
  email_record.Platform
FROM
  your_source_table,
  LATERAL VIEW EXPLODE(
    from_json(
      cdi_sentemailstatisticsdata,
      'STRUCT<list: ARRAY<STRUCT<Count: INT, EmailClient: STRING, Platform: STRING>>, lastUpdate: TIMESTAMP>'
    ).list
  ) exploded AS email_record

关键细节解释 #

• from_json：将JSON字符串转换为结构化数据，这里指定的schema精确匹配你的JSON结构——包含一个list数组（数组元素是带三个字段的结构体）和lastUpdate时间字段。
• LATERAL VIEW EXPLODE：将数组中的每个元素拆分为独立行，email_record是展开后单个数组元素的别名，方便后续提取字段。
• 字段映射：直接通过email_record访问数组元素内的Count、EmailClient、Platform，同时保留原表的id和JSON中的lastUpdate字段。

格式适配调整 #

如果你的JSON字段类型与示例不符（比如Count是字符串、lastUpdate是普通字符串格式），只需修改from_json中的schema定义：

• 若Count为字符串：将Count: INT改为Count: STRING
• 若lastUpdate为字符串：将lastUpdate: TIMESTAMP改为lastUpdate: STRING

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