Unnest array of structure in Presto

Question

I have the following data in a json format

create table events (
id_user bigint,
experiments ARRAY <
    STRUCT <
        id: BIGINT,
        impressed: BOOLEAN,
        variantId: BIGINT
    >
>
)

describe events returns:

columns               | types
id_user               | bigint
experiments           | array(row(id bigint, impressed boolean, variantid bigint)) 

I want to unnest the array struct with the following command

select CAST(ROW(array[experiments]) AS ROW(id BIGINT, impressed boolean, variantid bigint)) as test

from events

and presto returns the following error: failed: the size of fromType and toType must match

When I enter dummy data in the array the command runs smoothly.

What is the problem and how i overcome it?

Answer 1

presto returns the following error: failed: the size of fromType and toType must match

This:

ROW(array[experiments])

constructs a ROW with one field, of type "array of row"

I want to unnest the array

If you want to unnest the array, you want something like that:

SELECT *
FROM events
LEFT JOIN UNNEST(experiments) AS t(experiment) ON true

(for older Presto versions use CROSS JOIN instead of LEFT JOIN .. ON true; mind that this changes semantics)