How to apply filter in WHERE clause for Unnested Value in Big Query without using Inner query or CTE?

Question

I want to filter by the output of unnest but having an error

Unrecognized name: customer_id at [23:73]

 SELECT
          fullVisitorId,
          totals.transactionRevenue,

       (SELECT 
    MAX(
    IF
      (index=3,
        value,
        NULL))
  FROM
    UNNEST(hits.customDimensions)) AS customer_id

 FROM `XXXX.google_analytics.google_analytics_fact` as GA,
 UNNEST(GA.hits) as hits
  WHERE customer_id IS NOT NULL

I can filter the output with inner query or CTE but I am looking for a way to apply filter without using them.

Answer 1

That's because it doesn't exist in the table - SELECT gets executed last.

But you can use subqueries in WHERE

 SELECT
   fullVisitorId,
   totals.transactionRevenue,

   (SELECT value FROM UNNEST(hits.customDimensions) WHERE index=3) AS customer_id

 FROM `XXXX.google_analytics.google_analytics_fact` as GA,
   UNNEST(GA.hits) as hits
 WHERE (SELECT value FROM UNNEST(hits.customDimensions) WHERE index=3) IS NOT NULL