How do I get BigQuery to filter on repeated STRING field with IN and NOT IN?

user_3623641 0
Viewed 309

I am attempting to filter rows in a BigQuery table with a repeated string field. I want to filter based on rows that have at least one value from one predefined list, but does not contain any values from a second predefined list.

This is my query:

   SELECT em.asin, em.category 
    FROM 
        `my_proj.amazon_data.electronics_meta` as em,
        UNNEST(em.category) as sub_cat
    WHERE sub_cat IN ('Television & Video', 'Televisions', 'Television & Video')
    AND sub_cat NOT IN (
        'DVD Players & Recorders',
        'Projection Screens',
        'VCRs',
        'Blu-ray Players',
        'Blu-ray Players & Recorders'
    )

This is the result that I get: query_results

I would expect that the first row does not appear because it contains 'VCRs' in the category field.

I have also tried

AND LOWER(TRIM(sub_cat)) NOT IN (
        'dvd players & recorders',
        'projection screens',
        'vcrs',
        'blu-ray players',
        'blu-ray players & recorders'
    )

and

    AND NOT (sub_cat IN (
        'DVD Players & Recorders',
        'Projection Screens',
        'VCRs',
        'VCRs',
        'Blu-ray Players',
        'Blu-ray Players & Recorders'
    ))

But this does not change the result.

Additional Info: This is the schema for the table: table schema

google-bigquery sql
2 Answers

Use EXISTS:

SELECT em.asin, em.category 
FROM 
  `my_proj.amazon_data.electronics_meta` as em
WHERE 
  exists ( select *
           from unnest(category) as sub_cat
           where sub_cat IN ('Television & Video', 'Televisions', 'Television & Video')
         )
  and not exists ( select *
                   from unnest(category) as sub_cat
                   where sub_cat IN (
                     'DVD Players & Recorders',
                     'Projection Screens',
                     'VCRs',
                     'Blu-ray Players',
                     'Blu-ray Players & Recorders'
                   )
                 )
user_134735250

You can also do this in the FROM clause using aggregation:

SELECT em.asin, em.category 
FROM `my_proj.amazon_data.electronics_meta` em CROSS JOIN
     (SELECT COUNTIF(sub_cat IN ('Television & Video', 'Televisions', 'Television & Video')) as cnt_required,
             COUNTIF( 'DVD Players & Recorders', 'Projection Screens', 'VCRs', 'Blu-ray Players', 'Blu-ray Players & Recorders'
                    ) as cnt_not_allowed
      FROM unnest(category) cat
     ) c
WHERE c.cnt_required > 0 AND c.cnt_not_allowed = 0;
user_11440350
Related