Query Last Value in Fixed Length Array With Empty Values

Question

I have a table that generally looks like:

Name varchar       | Location varchar[]
---------------------------------------------
Lemoney    | {Home,Work,Home,Stack Exchange,,,,,,}
Other Dude | {Home,,,,,,,,,}
Busy Dude  | {Home,Work,Work,Work,Home,Work,,,,}

I have been trying to write a query that will return the Last Value for each Name like:

Lemony     | Stack Exchange
Other Dude | Home
Busy Dude  | Work

I haven't been able to figure out how to return that yet. Does anyone have any ideas?

Answer 1

SELECT distinct Name, loc 
FROM (
  SELECT  *, last_value(Location) over (partition by Name) as loc 
    FROM (SELECT Name, unnest(Location) as Location FROM table)  x) y;

Should answer your question