row_number full joins to emulate join by ordinality for several nested to-many collection fetches

gistfile1.txt

-- 1 parent 2 child collections
select 
  f.*,
  a.*,
  c.*
from (
  select 
    f.film_id,
    f.title,
    1 as f_rn
  from film as f
) as f
  full join (
    select
      a.actor_id,
      a.first_name,
      a.last_name,
      fa.film_id,
      row_number () over (partition by fa.film_id) as fa_rn
    from
      actor as a
        join film_actor as fa
        on a.actor_id = fa.actor_id 
  ) as a
  on f.film_id = a.film_id
  and f.f_rn = a.fa_rn
  full join (
    select
      c.category_id,
      c.name,
      fc.film_id,
      row_number () over (partition by fc.film_id) as fc_rn
    from
      category as c 
        join film_category as fc
        on c.category_id = fc.category_id  
  ) as c
  on coalesce(f.film_id, a.film_id) = c.film_id
  and coalesce(f.f_rn, a.fa_rn) = c.fc_rn
where f.film_id = 3
order by coalesce(f.film_id, a.film_id, c.film_id), coalesce(f_rn, fa_rn, fc_rn)

-- 1 parent 1 child collection with 1 nested child collection
select *
from (
  select 
    a.actor_id,
    a.first_name,
    a.last_name,
    1 as a_rn
  from actor as a
) as a
  full join (
    select
      f.film_id,
      f.title,
      f.category_id,
      f.name,
      fc_rn,
      fa.actor_id,
      row_number () over (partition by fa.actor_id) as fa_rn
    from (
      select 
        coalesce(f.film_id, c.film_id) as film_id,
        f.title,
        c.category_id,
        c.name,
        c.fc_rn
      from (
        select 
          f.film_id,
          f.title,
          1 as f_rn
        from film as f
      ) as f
        full join (
          select
            c.category_id,
            c.name,
            fc.film_id,
            row_number () over (partition by fc.film_id) as fc_rn
          from
            category as c 
              join film_category as fc
              on c.category_id = fc.category_id  
        ) as c
        on f.film_id = c.film_id
        and f.f_rn = c.fc_rn
    ) as f
      join film_actor as fa
      on f.film_id = fa.film_id
  ) as f
  on a.actor_id = f.actor_id
  and a.a_rn = fa_rn
where coalesce(a.actor_id, f.actor_id) = 19
order by coalesce(a.actor_id, f.actor_id), film_id, category_id