Postgres sql to perform join

Just general question, can anyone pls help how to perform left join on union data. I have query where I am doing union of data(using table1 and table2) based on few conditions now I need to perform left outer join with third table(table5) ,so I am looking for help how to do it. Just sample query will be fine.

I have created scenario on fiddle where I have done union based on few conditions using table 1 and table 2.https://dbfiddle.uk/WN6uC5MH Now I need to use output of union and perform left join on third table i.e table 5.So I need to do left join on field 6 of table 5 with output field 1 of union data if it matches then insert rows in table 5 all values will remain same except field 9 which we will get from union output data(field3):

Output will look like this :

'j','abc','def','xyz_inc'
'j','abc','def','5_abc'
'j','abc','def','6_abc'

1. You can translate() once instead of calling replace() twice. You can also merge your translate() calls instead of nesting those.
2. You can regexp_split_to_table() directly instead of wrapping unnest(string_to_array()).
3. As @JNevill pointed out, all you need to do is nest the query you have, either in a CTE or a subquery, then join to that.

Demo at db<>fiddle:

with your_big_union as (
    select field1,field2,string_agg(distinct fld,',') field3
    from(
        select *
        from(
            select field1,field2,fld
            from table1 t1
            left join (
                select * from table2
                cross join regexp_split_to_table(trim(translate(field4,'{}','')),',') fld
              )t2
              on concat(t1.field2,';') similar to concat('%',t2.field3,'[();]%')
        )x
    )b
      where field2 like '%fn@_%' escape '@' and fld is not null
    group by field1,field2
    union
    (with cte as (
        select field1,field2
              ,''''
               ||translate( btrim(field2)
                            ,E'\n"'''
                            ,'' )
               ||''''  as edited_field2 
      from table1)
    ,cte2 as (
        select *,t.spotted_table as spotted_target,s.spotted_table as spotted_source
        from cte
        left join regexp_matches(
             edited_field2
            ,'(?:UPDATE|INTO)(?:\s+ONLY)?\s+([[:alpha:]_]+[\.\w]*|"[^"]+"(?:\."[^"]+")?)'
            ,'gi'
          ) with ordinality as target_matches(hits,n1) on true
      left join unnest(target_matches.hits) with ordinality as t(spotted_table,n2) on true
      left join   regexp_matches(
             edited_field2
            ,'(?:FROM|JOIN|USING|TABLE)(?:\s+ONLY)?\s+([[:alpha:]_]+[\.\w]*|"[^"]+"(?:\."[^"]+")?)'
            ,'gi'
          ) with ordinality as source_matches(hits,n1) on true
      left join unnest(source_matches.hits) with ordinality as s(spotted_table,n2) on true)
    select field1
          ,string_agg(distinct spotted_target,',') as spotted_targets
         -- ,string_agg(distinct spotted_source,',') as spotted_sources
          ,edited_field2
    from cte2 where edited_field2 not like '%fn@_%' escape '@' and spotted_target is not null
    and coalesce(split_part(spotted_target,'.',1) !~* '_inc"?$',true)
      and coalesce(split_part(spotted_source,'.',1) !~* '_inc"?$',true)
    group by field1,edited_field2
    order by field1))
select * 
from table5 left join your_big_union 
on lower(your_big_union.field1) = table5.field6;

4. If table5.field6 joins to field1, you'll get no matches because you uppercased your 'J' in field, while field6 holds a lowercase. I'm not sure why you expect 3 results if you only have a single 'j' on both sides of the join.