Athena/Presto - UNNEST MAP 到列 [英] Athena/Presto - UNNEST MAP to columns

问题描述

假设我有一张这样的桌子，

Assume i have a table like this,

表:qa_list

    id   |  question_id |  question  | answer    |  
---------+--------------+------------+-------------
    1    |  100         |  question1 | answer    |  
    2    |  101         |  question2 | answer    |  
    3    |  102         |  question3 | answer    |  
    4    |  ... 
    ...  |  ...

以及给出以下结果的查询(因为我找不到转置表的直接方法)，

and a query that gives below result (since I couldn't find a direct way to transpose the table),

表:qa_map

   id   |  qa_map
--------+---------
    1   | {question1=answer,question2=answer,question3=answer, ....}

其中 qa_map 是任意数量的问题和答案的 map_agg 的结果.

Where qa_map is the result of a map_agg of arbitrary number of questions and answers.

有没有办法将 qa_map UNNEST 映射到任意数量的列，如下所示?

Is there a way to UNNEST qa_map to an arbitrary number of columns as shown below?

    id   |  Question_1 |  Answer_1 |  Question_2 |  Answer_2 |  Question_3 | ....
---------+-------------+-----------+-------------+-----------+-------------+
    1    |  question   | answer    |  question   | answer    |  question   | ....

AWS 雅典娜/Presto-0.172

AWS Athena/Presto-0.172

推荐答案

不，没有办法编写导致根据数据产生不同列数的查询.在查询执行开始之前必须知道这些列.您拥有的地图与您将要获得的地图一样接近.

No, there is no way to write a query that results in different number of columns depending on the data. The columns must be known before query execution starts. The map you have is as close as you are going to get.

如果您将想要这样做的动机包括在内，我们可能会通过其他方式帮助您实现最终目标.

If you include your motivation for wanting to do this there may be other ways we can help you achieve your end goal.

这篇关于Athena/Presto - UNNEST MAP 到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！