presto/trino - Get key of mapped element given a value

Question

I have a database of surveys, where there are tables for survey, answers and questions.

In the main query, I want to get each answer in a different column and do some manipulation on the answer, but the order of the answers may vary in time (in a way that the creator of the survey may change the order of the questions). That way, I can't know which answer is being modified.

Each answer corresponds to a question with pk, and the questions has a column to set the order between them. That means that the question pk is the parameter I should decide on how to manipulate a certain answer.

for example: I have a table survey:

|  pk | answers
+-----+---------
|  1  |[1,2,3]
+-----+---------
|  2  |[4,5,6]
+-----+---------

The second table is answer: (Pay attention that the question order differentiate between the two surveys)

|  pk | text |question
+-----+------+---------
|  1  |'ans1'|1
|  2  |'ans2'|2
|  3  |'ans3'|3
|  4  |'ans4'|3
|  5  |'ans5'|2
|  6  |'ans6'|1
+-----+------+------

And the third table question:

|  pk | question            |order
+-----+---------------------+------
|  1  |"What is your name?" |  3
+-----+---------------------+------
|  2  |"What is your age?"  |  2
+-----+---------------------+------
|  3  |"Where do you live?" |  1
+-----+---------------------+------

I am trying to get the answers in a fixed position so I'll be able to know the exact place where to modify the answer.

I have a subquery with two mapped elements:

select s.pk, map_agg(t.index, a.text) t_map, map_agg(t.index, a.question) q_map
        from survey s
        cross join unnest(s.answers) with ordinality as t(a, index)
        join answer a on t.a = a.pk
        group by s.pk

In the main query I want to get from q_map the index in which a certain value is. That index is the same index where the corresponding answer text is in t_map.

So I am trying to do something like:

SELECT 
    pk,
    t_map[get_key_by_val(q_map, 1)] ans1,
    t_map[get_key_by_val(q_map, 2)] ans2,
    t_map[get_key_by_val(q_map, 3)] ans3
    from ( ... 
              --subquery
         )

Or if there is any other way of doing so.

---

EDIT: I have managed to do something like this, but this is a very bad solution, especially when there are more than 10 question:

    CASE WHEN q_map[1] = 1 THEN t_map[1] 
        ELSE (CASE WHEN q_map[2] = 1 THEN t_map[2]
        ELSE (CASE WHEN q_map[3] = 1 THEN t_map[3]
        END)END)END as answer1

and I have to do so for each column.

Answer 1

You can use primary key in question table as constant index for your query. With that in mind, you can write the query as below:

WITH survey_w_fixed_layout AS (
    SELECT
        s.pk as survey_id,
        map_agg(a.question, q.question) as question_map_by_q_pk,
        map_agg(a.question, a.text) as answer_map_by_q_pk
    FROM survey s
    CROSS JOIN unnest(s.answers) as t(a)
    JOIN answer a on t.a = a.pk
    JOIN question q on a.question = q.pk
    GROUP BY s.pk
)
SELECT
    survey_id,
    answer_map_by_q_pk[1] as ans1,
    answer_map_by_q_pk[2] as ans2,
    answer_map_by_q_pk[3] as ans3
FROM
    survey_w_fixed_layout