BigQuery中customDimensions IS NOT NULL过滤条件不生效问题咨询

核心问题原因 #

• WHERE子句的过滤条件仅作用于UNNEST(customDimensions)展开后的单条自定义维度行的value非空，这个逻辑和SELECT子句中取PCF_CUST_ID的逻辑完全无关联：你取PCF_CUST_ID是从原始行t的完整customDimensions嵌套数组里查询index=4对应的value，但WHERE里过滤的展开行不一定是index=4的自定义维度。
• 举个实际场景：某条访问记录的customDimensions数组包含两个元素：index=2、value="test"和index=4、value=null。UNNEST后index=2的行满足value IS NOT NULL的过滤条件会被保留，此时SELECT子句从原数组取index=4的value就会返回NULL，最终结果就会出现PCF_CUST_ID为空的记录。
• 额外问题：当前写法会导致同一条visitId产生重复行，只要该访问有任意一个非空的自定义维度，就会返回对应数量的重复行，每行的PCF_CUST_ID取值一致。

修正后的SQL写法 #

你可以直接在UNNEST的自定义维度中过滤index=4的行，不需要额外写子查询，过滤逻辑直接作用于目标字段即可：

create table `putput_folder.output_data` as
select 
  visitId,
  customDimensions.value AS PCF_CUST_ID
from `input_folder.input_data_*` as t, 
UNNEST(customDimensions) as customDimensions
where 
  _TABLE_SUFFIX between '20210101' and '20210131' 
  and customDimensions.index = 4
  and customDimensions.value is not null
order by visitId;

如果需要保留所有visitId、仅将不符合条件的PCF_CUST_ID置空，可以改用LEFT JOIN UNNEST的写法：

create table `putput_folder.output_data` as
select 
  visitId,
  cd.value AS PCF_CUST_ID
from `input_folder.input_data_*` as t
left join UNNEST(customDimensions) as cd
  on cd.index = 4 and cd.value is not null
where 
  _TABLE_SUFFIX between '20210101' and '20210131'
order by visitId;

内容的提问来源于stack exchange，提问作者new_commer1218