with account_session_ste as ( SELECT * FROM `table` where exists(select 1 from unnest(hits) h where regexp_contains(h.page.hostname, r'signin.account.gov.uk')) and _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY)) ) -- group by 1,2) select fullVisitorId AS user_id, hits.page.hostname as hostname, hits.page.pagePath as pagePath, hits.hitNumber as hit_number -- * from account_session_ste, unnest(hits) as hits