Using UNNEST Function in BigQuery

Question

I need help on how to use BigQuery UNNEST function. My query:

I have table as shown in the image and I want to unnest the field "domains" (string type) currently separated by comma, so that I get each comma separated domain into a different row for each "acname". The output needed is also enclosed in the image:

enter image description here

I tried this logic but did not work:

select acc.acname,acc.amount,acc.domains as accdomains from project.dataset.dummy_account as acc CROSS JOIN UNNEST(acc.domains)

But this gave error "Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRING". The error makes sense completely but did not understand, how to convert string to an array.

Can someone please help with solution and also explain a bit, how actually it works. Thank you.

What data type of domains column? Is it string or array(repeated string)? — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-08-22T05:24:46

Below is for BigQuery Standard SQL

#standardSQL
SELECT acname, amount, domain
FROM `project.dataset.dummy`,
UNNEST(SPLIT(domains)) domain

You can test, play with above using dummy data from your question as in example below

#standardSQL
WITH `project.dataset.dummy` AS (
  SELECT 'abc' acname, 100 amount, 'a,b,c' domains UNION ALL
  SELECT 'pqr', 300, 'p,q,r' UNION ALL
  SELECT 'lmn', 500, 'l,m,n'
)
SELECT acname, amount, domain
FROM `project.dataset.dummy`,
UNNEST(SPLIT(domains)) domain

with output

Row acname  amount  domain   
1   abc     100     a    
2   abc     100     b    
3   abc     100     c    
4   pqr     300     p    
5   pqr     300     q    
6   pqr     300     r    
7   lmn     500     l    
8   lmn     500     m    
9   lmn     500     n

The source table project.dataset.dummy which had field "domains" has comma separated values but after the comma there is a space (e.g. 'a'commaspace'b'commaspacec a, b, c). This results in space before the values b c q r m n; in the field "domains" in "Output After Unnest" table. Now I'm joining this table with "salesdomain" as a key. But because of space before b c q r m n, the output received is not correct

To address this - you can just simply use TRIM function to removes all leading and trailing spaces, like in example below

#standardSQL
WITH `project.dataset.dummy` AS (
  SELECT 'abc' acname, 100 amount, 'a, b, c' domains UNION ALL
  SELECT 'pqr', 300, 'p, q, r' UNION ALL
  SELECT 'lmn', 500, 'l, m, n'
)
SELECT acname, amount, TRIM(domain, ' ') domain
FROM `project.dataset.dummy`,
UNNEST(SPLIT(domains)) domain

Using UNNEST Function in BigQuery

1 Answers