Sql 在 Athena Presto 中提取 JSON 对象以获取动态密钥

发布于04月01日

我的表中有两个string格式的列.其中一列包含json对象.JSON对象中的键是不固定的-所以问题是我不能使用标准的JSON提取函数.下面是表格的外观

timestamp	upstream_tables
2023-02-02T17:34:55.946Z	{"ETL_table_1":true,"ETL_table_2":true}
2023-02-02T13:30:11.882Z	{"ETL_table_3":true}
2023-02-02T20:12:22.116Z	{"ETL_table_4":true,"ETL_table_5":true,"ETL_table_6":false}

我想把这张桌子压平成下面这样的样子

timestamp	upstream_tablename	was_completed
2023-02-02T17:34:55.946Z	ETL_table_1	TRUE
2023-02-02T17:34:55.946Z	ETL_table_2	TRUE
2023-02-02T13:30:11.882Z	ETL_table_3	TRUE
2023-02-02T20:12:22.116Z	ETL_table_4	TRUE
2023-02-02T20:12:22.116Z	ETL_table_5	TRUE
2023-02-02T20:12:22.116Z	ETL_table_6	FALSE

有人能帮帮忙吗？我花了很多时间使用map_values和map_key，但就是做不对.

我唯一能想到的最接近的东西就是这个

select
   timestamp,
    t.*
FROM mytable
   CROSS JOIN UNNEST(map_entries(CAST(json_extract(upstream_tables, '$') AS MAP(VARCHAR, VARCHAR)))) AS t

WITH data(ts, value) AS ( VALUES (from_iso8601_timestamp('2023-02-02T17:34:55.946Z'), VARCHAR '{"ETL_table_1":true,"ETL_table_2":true}'), (from_iso8601_timestamp('2023-02-02T13:30:11.882Z'), VARCHAR '{"ETL_table_3":true}'), (from_iso8601_timestamp('2023-02-02T20:12:22.116Z'), VARCHAR '{"ETL_table_4":true,"ETL_table_5":true,"ETL_table_6":false}') ) select ts, t.* FROM data CROSS JOIN UNNEST(CAST(json_parse(value) AS MAP(VARCHAR, JSON))) AS t(upstream_tablename, was_completed);

ts	upstream_tablename	was_completed
2023-02-02 17:34:55.946 UTC	ETL_table_1	true
2023-02-02 17:34:55.946 UTC	ETL_table_2	true
2023-02-02 13:30:11.882 UTC	ETL_table_3	true
2023-02-02 20:12:22.116 UTC	ETL_table_4	true
2023-02-02 20:12:22.116 UTC	ETL_table_5	true
2023-02-02 20:12:22.116 UTC	ETL_table_6	false

upstream_tablename

was_completed

2023-02-02 17:34:55.946 UTC

ETL_table_1

true

2023-02-02 17:34:55.946 UTC

ETL_table_2

true

2023-02-02 13:30:11.882 UTC

ETL_table_3

true

2023-02-02 20:12:22.116 UTC

ETL_table_4

true

2023-02-02 20:12:22.116 UTC

ETL_table_5

true

2023-02-02 20:12:22.116 UTC

ETL_table_6

false

Sql 在 Athena Presto 中提取 JSON 对象以获取动态密钥

推荐答案

Sql相关问答推荐

postgresql插入json不工作

如何在SQL Server中统计按备注分组的记录数

缺少日期标识

按两列分组，并根据SQL中的条件返回第三个列值

在子窗口SQL Presto中使用特定条件执行值计数

显示十进制列，但尽可能显示为整数

如何根据 SQL 中的阈值标记一个集群中的所有值？

在 SQL Server 中合并两个 XML 列

在 PostgreSQL 中，如何让多个判断约束引用相同的值数组？

如何使用SELECT语句进行左连接，并根据右表中的特定值过滤结果？

标量子查询中的窗口函数不起作用

SQL Server：将JSON对象数组转换为表格格式

在Snowflake中如何使用SQL对版本字符串进行排序？

String_Split 多列

如何 Select 一列具有最小值而另一列具有给定值的记录？

Oracle SQL 从多个条件中 Select 但具有相同的 id

SELECT 用于 Parent、Children 和 ORDER BY [Order] 列

在给定列中具有特定值的行与 SQL 中的总行数的比率

如果当前日期是一周中的某一天，则从另一天提取结果

使用 SQL 表中的连接列删除重复记录