When doing UNION with SUPER datatype in redshift, it implicitly converts SUPER data to VARCHAR before calling REGEXP_SUBSTR. This way it loses hierarchical structure

Instead of that below query should work where we are maintaining SUPER struct till it reaches REGEXP_SUBSTR and then explicitly converting datatype of logs.attributes.http.url_details.path to VARCHAR.

with all_logs as (
    (select * from  "xxx"."xxx"."xxx" limit 10)
    union
    (select * from  "xxx"."xxx"."xxx" limit 10))
SELECT
    logs.attributes.http.method AS http_method,
    REGEXP_SUBSTR(logs.attributes.http.url_details.path::VARCHAR, '(firm/)?(v[0-9]/)')
    FROM all_logs as logs

reference: https://docs.aws.amazon.com/redshift/latest/dg/query-super.html#dynamic-typing-lax-processing