CODEWITHSUNDEEP
google-bigquery
AmirCS
AmirCS

Reputation: 321

UNNEST Multiple Fields From the same Table - BigQuery

I would like to UNNEST both Info2 and Info3 for the following query. How can I do this for the same table in BigQuery. Info3 also has a condition similar to info2.

Thanks!

ID | startDate | endDate | info1 | info2 | info 3  
----------------------------------------------------
1  | 11-12-2000 | 11-12-2010 | Blue   | Circle   | A
                                      | Triangle | B
                                      | Square   | 
----------------------------------------------------
2  | 11-12-2001 | 11-12-2011 | Yellow |    <*>   | C  
----------------------------------------------------
3  | 11-12-2007 | 11-12-2008 | Brown  |  Circle  | D
                                      | Triangle | B    
----------------------------------------------------

info2!="<*>" AND inf3=B

ID | startDate | endDate | info1 | info2 | info 3  
----------------------------------------------------
1  | 11-12-2000 | 11-12-2010 | Blue   | Circle   | B
1  | 11-12-2000 | 11-12-2010 | Blue   | Triangle | B
1  | 11-12-2000 | 11-12-2010 | Blue   | Square   | B
----------------------------------------------------
3  | 11-12-2007 | 11-12-2008 | Brown  |  Circle  | B
3  | 11-12-2007 | 11-12-2008 | Brown  | Triangle | B
----------------------------------------------------

The following query would work w/o Info3.

#standard_SQL    
SELECT
        id,
        startDate,
        endDate,
        info1,
        info2,
        info3
      FROM
         `MY_DB`
      WHERE
        EXISTS (
        SELECT
          Info2
        FROM
          UNNEST(Info2) Temp
        WHERE
          Temp NOT IN ("<*>"))

Upvotes: 5

Views: 22343

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.MY_DB` AS (
  SELECT 1 id, '11-12-2000' startDate, '11-12-2010' endDate, 'Blue' info1, ['Circle','Triangle', 'Square'] info2, ['A', 'B'] info3 UNION ALL
  SELECT 2, '11-12-2001', '11-12-2011', 'Yellow', ['<*>'], ['C'] UNION ALL  
  SELECT 3, '11-12-2007', '11-12-2008', 'Brown', ['Circle','Triangle'], ['D', 'B'] 
)
SELECT id, startDate, endDate, info1, info2, info3
FROM `project.dataset.MY_DB`, UNNEST(info2) info2, UNNEST(info3) info3
WHERE info2 != '<*>' AND info3 = 'B'
ORDER BY id

with result as below 

Row id  startDate   endDate     info1   info2       info3    
1   1   11-12-2000  11-12-2010  Blue    Circle      B    
2   1   11-12-2000  11-12-2010  Blue    Triangle    B    
3   1   11-12-2000  11-12-2010  Blue    Square      B    
4   3   11-12-2007  11-12-2008  Brown   Circle      B    
5   3   11-12-2007  11-12-2008  Brown   Triangle    B

Upvotes: 11

Related Questions