12 September 2018 · victor ·       Add to Favorites   Report

Using UNNEST Command in Google BigQuery

Sample Table:

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
----------------------------------------------------

Solution:

To expand the array columns, you can use the UNNEST Command

#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 the 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

victor

posted on 12 September 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied