How to convert Array of columns to rows in Bigquery

Question

I have a column as below screenshot in my google bigquery

i need to convert that column to rows as below in Bigquery :

70042 70055 70044 70046 70042 70055 70044 70046

Please suggest me how can i get the rows like above.

Answer 1

Below examples for BigQuery Standard SQL
First is applicable if your column is an array of string and second in case if it is a string that looks like array :o)

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, ['70042', '70055', '70044', '70046'] dspid UNION ALL
  SELECT 2 id, ['70042', '70055', '70044', '70046'] dspid 
)
SELECT id, dspid
FROM `project.dataset.table`,
UNNEST(dspid) dspid   


#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '["70042","70055","70044","70046"]' dspid UNION ALL
  SELECT 2 id, '["70042","70055","70044","70046"]' dspid 
)
SELECT id, dspid
FROM `project.dataset.table`,
UNNEST(SPLIT(REGEXP_REPLACE(dspid, r'[\[\]"]', ''))) dspid   

Both produce below result

Row id  dspid    
1   1   70042    
2   1   70055    
3   1   70044    
4   1   70046    
5   2   70042    
6   2   70055    
7   2   70044    
8   2   70046      

Note: below fragment is used just to mimic you real data / table and just for you to test / play with. In reality, you can remove it and use your specific project.dataset.table

WITH `project.dataset.table` AS (
  ..............
)

Next time you ask question on SO please provide more details about your case and show what you tried even if it didn't work for you - this usually helps us to better understand your case and thus better help you

Update

So, for example, if your table is veuhub-185502.AdtechAnalytics.tag_request you should use below

#standardSQL
SELECT id, dspid
FROM `veuhub-185502.AdtechAnalytics.tag_request`,
UNNEST(dspid) dspid 

or below for (based on your comments)

#standardSQL
SELECT 
  DSPID, 
  adtype, 
  adtypeWithDevice, 
  EXTRACT(year FROM request_timestamp) AS year, 
  EXTRACT(month FROM request_timestamp) AS month, 
  EXTRACT(day FROM request_timestamp) AS day, 
  EXTRACT(hour FROM request_timestamp) AS hour, 
  SUM(1) AS requestcount 
FROM `veuhub-185502.AdtechAnalytics.tag_request`, 
UNNEST(dspid) dspid 
GROUP BY adtype,adTypeWithDevice,DSPID,year,month,day,hour