在Athena/Presto中提取含动态键的JSON对象并展平表结构

你已经找对方向啦！用map_entries配合UNNEST的思路完全没问题，只是在类型转换和字段提取上稍作调整就能得到你想要的展平结果。

针对你的需求，这里给出完整的SQL查询语句：

SELECT
  timestamp,
  t.key AS upstream_tablename,
  t.value AS was_completed
FROM mytable
CROSS JOIN UNNEST(map_entries(CAST(json_parse(upstream_tables) AS MAP(VARCHAR, BOOLEAN)))) AS t

关键步骤解释： #

• JSON转MAP：用json_parse(upstream_tables)把JSON字符串解析成JSON对象，再通过CAST(...) AS MAP(VARCHAR, BOOLEAN)转换成键为字符串、值为布尔类型的MAP——因为你的JSON值都是true/false，用BOOLEAN类型比VARCHAR更贴合实际数据类型。
• MAP转键值对数组：map_entries()函数会把MAP转换成一个数组，数组里的每个元素是包含key和value的结构体，比如{"ETL_table_1":true}会变成[{'key': 'ETL_table_1', 'value': true}]。
• 数组拆分成行：CROSS JOIN UNNEST(...)会把数组里的每个元素拆成单独的行，这样原表中一行的多个JSON键值对就会变成多行，同时保留原有的timestamp字段。
• 提取字段：最后直接从结构体t中取出key和value，重命名成你需要的upstream_tablename和was_completed即可。

如果你的JSON值存在其他类型（比如字符串型的"true"/"false"），可以把MAP的value类型改成VARCHAR，之后再用CAST(t.value AS BOOLEAN)转换类型，适配你的实际数据情况。

备注：内容来源于stack exchange，提问作者chuzzle