Querying Nested Type Map <string, timestamp>

Question

I have a sample table, let's call it table:

id	events
1	{requested=1719-05-08 11:11:11.111, created=1820-05-08 11:11:11.699, completed=1869-69-69 15:11:13.111}
2	{created=6969-01-01 09:01:01.466}

I want to be able to query the completed date in each ID but not all ID's contain a completed timestamp.

What I've tried:

select events["completed"] as completed from table 

My error: Column 'completed' cannot be resolved.

Desired Output:

events
1869-69-69 15:11:13.111

Can someone assist please? Thank you.

Answer 1

SELECT ELEMENT_AT(events, 'completed') as completed_at from table

https://trino.io/docs/current/functions/map.html

Answer 2

Double quotes escape a column name (if it has some invalid symbols in it), try passing string (with single quotes):

select events['completed'] as completed 
from table 

Note that it will throw if key is not present in the map. You can use element_at if the key can be missing:

select element_at(events, 'completed') as completed 
from table