Extract from Array of Rows in Presto

Question

I have a table that has a list of dictionaries. In the example there is only one dictionary in the list.

items
[{'a': 'apple', b: 'banana'}]

I want to sekect the value for 'a', which is apple.

I have tried: SELECT json_extract_scalar(json_parse(items), '$.a') which gives the error:

Unexpected parameters (array(row("t" varchar,"i" varchar,"p" integer,"v" integer,"s" varchar))) for function json_parse. Expected: json_parse(varchar(x))

I also tried: SELECT TRANSFORM(CAST(JSON_PARSE(items) AS ARRAY<JSON>), x -> JSON_EXTRACT_SCALAR(x, '$.a'))

which gives the same error. If I do CAST(items AS varchar) first, there is an error that says it cannot convert an array to varchar.

Answer 1

Provided error shows that your data is not actually a varchar or json but an array of ROW with 5 fields (t, i, p, v, s). Rows can't be easily converted to required json structure, but you can easily access it's fields by name:

select item.t
from (
    SELECT array [ cast (row('apple', 'banana') as row(t varchar, i varchar)) ] items -- shortened sample data
) 
cross join unnest(items) as c(item) -- flatten the array

Output:

t
apple