时间戳

首页 > 解决方案 > BigQuery - 过滤器从嵌套表中无效

sql - BigQuery - 过滤器从嵌套表中无效

问题描述

我有这样的嵌套表：

BASKET-LEVEL                  PRODUCT_LEVEL             PAYMENT_LEVEL
id, gross_value,is_voided     gross_value, is_voided    amount, is_voided
                              gross_value, is_voided    amount, is_voided

我需要确保is_voided = true在每个粒度级别都过滤掉了。

解决这个问题的最佳方法是什么？>

SELECT BL.* FROM BASKET-LEVEL AS BL
LEFT JOIN UNNEST(PRODUCT_LEVEL) AS PROD
LEFT JOIN UNNEST(PAYMENT_LEVEL) AS PAY
WHERE BL.isVoided is false
AND PROD.isVoided is false
AND PAY.isVoided is false

标签： sqlgoogle-bigquerynested-table

解决方案

您可以使用EXISTS类似这样的子选择，该子选择使用UNNEST并解决属性

SELECT
* 
from `table`
  WHERE NOT EXISTS 
      (
       SELECT 1 FROM UNNEST (product_level)  
       WHERE is_voided is true
      )
  AND NOT EXISTS
      (
       SELECT 1 FROM UNNEST (payment_level)  
       WHERE is_voided is true
      )

推荐阅读