How to find max value for id appearing anywhere in another column?

Question

I hope that title is clear. If not, please change.

I have table, which look like this:

I would like to create a select query, where if gid is in anywhere in ancestor column, select max level from this rows. For example: Gid is 727 is four times in ancestor column. Max level from this rows is 3, so the value in the new column, let's says level_a, where gid is 727, will be 3. For gid 725 the same but for gid 717 will be 0.

I tried to use unnest, join, subquery but nothing worked. I have read about lateral join but I don't have any idea, how I can use it.

I'm using PostgreSQL 10 with PostGIS 2.5.2

Answer 1

Unnest is working when I try:

select id, coalesce(sub.level,0) maxlevel
from sometable
left join (
    select distinct on (1) 
        unnest(ancestor) id2, level
    from sometable
    order by 1,2 DESC) sub
on sometable.id = sub.id2