BigQuery - Filter voided from nested table

Question

I have nested table like this:

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

I need to make sure that I have filtered out is_voided = true at each level of granularity.

What is the best approach to this problem?>

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

Answer 1

you can use a EXISTS like this, with a subselect that uses UNNEST and address an attribute

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
      )