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 ("<*>"))
To convert an ARRAY into a set of rows, also known as "flattening," use the UNNEST operator. UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY . Because UNNEST destroys the order of the ARRAY elements, you may wish to restore order to the table.
OFFSET means that the numbering starts at zero, ORDINAL means that the numbering starts at one. A given array can be interpreted as either 0-based or 1-based. When accessing an array element, you must preface the array position with OFFSET or ORDINAL , respectively; there is no default behavior.
The UNNEST function returns a result table that includes a row for each element of the specified array. If there are multiple ordinary array arguments specified, the number of rows will match the array with the largest cardinality.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With