在Athena / Presto中为动态键提取JSON对象

b1payxdu 于 2023-04-08 发布在 Presto

关注(0)|答案(2)|浏览(296)

我的表中有两个string的列。其中一列中有json对象。JSON对象中的键不是固定的-所以问题是我不能使用标准的json提取函数。下面是表的外观
| 时间戳|上游表|
| --------------|--------------|
| 2023-02-02T17：34：55.946Z|© 2018 - 2019版权所有|
| 2023-02-02T13：30：11.882Z|产品名称：ETL_table_3|
| 2023-02-02T20：12：22.116Z|{“ETL表格4”：真，“ETL表格5”：真，“ETL表格6”：假}|
我想把这张table压平成下面这样
| 时间戳|上游表名|已完成|
| --------------|--------------|--------------|
| 2023-02-02T17：34：55.946Z|ETL_table_1|真|
| 2023-02-02T17：34：55.946Z|ETL_table_2|真|
| 2023-02-02T13：30：11.882Z|ETL_table_3|真|
| 2023-02-02T20：12：22.116Z|ETL_table_4|真|
| 2023-02-02T20：12：22.116Z|ETL_table_5|真|
| 2023-02-02T20：12：22.116Z|ETL_table_6|假的|
有人能帮忙吗？我花了很多时间使用map_values和map_keys，但没能把它弄对。
我能想到的最接近的就是这个

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

JSON

来源：https://stackoverflow.com/questions/75900420/extracting-json-objects-in-athena-presto-for-dynamic-keys

2条答案

按热度按时间

baubqpgj1#

@martin-traverso的答案可以用于基于特里诺的Athena引擎v.3，对于v.2和v.3，你已经发现了铸造到map的主要技巧，我会从使用json_extract切换到json_parse（从字符串转换为json），跳过map_entries（Presto/特里诺可以将Map解嵌套到键值对，可选地使用MAP(VARCHAR, JSON)作为目标类型）并指定解嵌套结果的列名，Presto/Trino可以将Map解嵌套到键值对：

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	上游表名	已完成
2023-02-02 17：34：55.946 UTC	ETL_table_1	真
2023-02-02 17：34：55.946 UTC	ETL_table_2	真
2023-02-02 13：30：11.882 UTC	ETL_table_3	真
2023-02-02 20：12：22.116 UTC	ETL_table_4	真
2023-02-02 20：12：22.116 UTC	ETL_table_5	真
2023-02-02 20：12：22.116 UTC	ETL_table_6	假的

赞(0）回复(0）举报 2023-04-08

n6lpvg4x2#

您可以使用UNNEST和json_query的组合来实现这一点。
首先，使用json_query将所有字段值对转换为形状为{"name": "xxx", value: yyy}的规范化形式。然后，将这些转换为行数组并将其解嵌套为单独的行。

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.name, t.value
FROM data, UNNEST(CAST(json_parse(json_query(value, 'strict $.keyvalue()' WITH ARRAY WRAPPER)) AS array(row(name varchar, value boolean)))) t(name, value)

赞(0）回复(0）举报 2023-04-08

我来回答

在Athena / Presto中为动态键提取JSON对象

2条答案

相关问题

热门标签

最新问答