sqlamazon-athenaprestotrinounnest

How can I query attributes with struck array data type?


I have this value with data type array<struct<id:string,name:string,values:array<string>>

[
    {id=gid://test/1234, name=Size, values=[L, M, S, XS]}, 
    {id=gid://test/12345, name=Color, values=[Black]}
]

How can I query to get the size value with output like this [L, M, S, XS]?

I tried this

SELECT 
   * 
FROM 
    mytable
CROSS JOIN UNNEST(options) AS t (option_struct)
CROSS JOIN UNNEST(option_struct.values) AS size_value
WHERE 
    option_struct.name = 'Size'

Solution

  • If the goal is to get the array you don't need to use second unnest:

    -- sample data
    with dataset(options) as(
     values (array[
            cast(ROW('gid://test/1234', 'Size', array['L', 'M', 'S', 'XS']) as ROW(id varchar, name varchar, "values" array(varchar))),
            cast(ROW('gid://test/12345', 'Color', array['Black']) as ROW(id varchar, name varchar, "values" array(varchar)))
        ])
    )
    
    -- query
    SELECT option_struct."values" as size_value
    FROM
        dataset
        , UNNEST(options) AS option_struct
    WHERE
        option_struct.name = 'Size';