大查询标准SQL中的子选择

Question

我似乎无法解决这个SQL问题。我有子选择查询，想添加一个where子句：

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

在外部where子句中，我想过滤一下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 AND articlePublishingDate is not null

但是，我收到了一个"无法识别的名称"错误。你知道怎么解决这个问题吗？

Answer 1

您可以尝试以下任何选项而不是AND articlePublishingDate is not null

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

或

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