CODEWITHSUNDEEP
sqlgoogle-bigquery
Andii
Andii

Reputation: 584

Sub select in Big Query Standard SQL

I can't seem to solve this SQL problem. I have sub select queries and would like to add a where clause:

SELECT 

  -- Custom Dimension Canonical URL (Hit)
 (
    SELECT
      value
    FROM
      UNNEST(hits.customDimensions)
    WHERE
      index = 1
    GROUP BY
      1
  ) AS canonicalURL,

  -- Custom Dimension Publishing Date (Hit)
  (
    SELECT
      value
    FROM
      UNNEST(hits.customDimensions)
    WHERE
      index = 8
    GROUP BY
      1
  ) AS articlePublishingDate

FROM
  ${constants.ga_tables} AS session,
  UNNEST(hits) AS hits
WHERE
  _table_suffix BETWEEN '20191103'
  AND FORMAT_DATE(
    '%Y%m%d',
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  )
  AND totals.visits = 1

GROUP BY
  1,
  2

In the outer where clause I would like to filter on articlePublishingDate is not null like so:

FROM
  ${constants.ga_tables} AS session,
  UNNEST(hits) AS hits
WHERE
  _table_suffix BETWEEN '20191103'
  AND FORMAT_DATE(
    '%Y%m%d',
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  )
  AND totals.visits = 1 AND articlePublishingDate is not null

However, I'm getting an "Unrecognized name" error. Do you have an idea how to fix this?

Upvotes: 0

Views: 64

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

instead of AND articlePublishingDate is not null you can try any of below options 

AND EXISTS (SELECT 1 FROM UNNEST(arr) WHERE index = 8)

or 

AND 8 IN (SELECT index FROM UNNEST(arr))

Upvotes: 1

Related Questions