How to associate multiple BigQuery array fields?

Question

I am working with a BigQuery data table that is using multiple array fields to store elements of the same logical record.

In this case there is one array field for ‘product code’, a second array field for ‘quantity’ and a third array field for ‘price’. All array fields will have great same number if elements.

How can I build a BigQuery query that extracts the product code, quantity and price as a three column result set?

You have a low rate. Important on SO - you can mark accepted answer by using the tick on the left of the posted answer, below the voting. See meta.stackexchange.com/questions/5234/… for why it is important! Also important to vote on answer. Vote up answers that are helpful. ... You can check about what to do when someone answers your question - stackoverflow.com/help/someone-answers. Following these simple rules you increase your own reputation score and at the same time you keep us motivated to answer your questions :o) please consider! — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-04-22T23:05:21

Below is for BigQuery Standard SQL

#standardSQL
SELECT id, rec.*
FROM `project.dataset.table`,
UNNEST(ARRAY(    
  SELECT AS STRUCT code, quantity, price
    FROM UNNEST(code) code WITH OFFSET
    JOIN UNNEST(quantity) quantity WITH OFFSET USING(OFFSET)
    JOIN UNNEST(price) price WITH OFFSET USING(OFFSET)  
)) rec

You can test, play with above using sample dummy data as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, ['product1', 'product2', 'product3'] code, [10, 20, 30] quantity, [2.99, 1.99, 0.99] price UNION ALL
  SELECT 2, ['product4', 'product5', 'product6'], [60, 50, 40], [3.99, 4.99, 5.99] 
)
SELECT id, rec.*
FROM `project.dataset.table`,
UNNEST(ARRAY(    
  SELECT AS STRUCT code, quantity, price
    FROM UNNEST(code) code WITH OFFSET
    JOIN UNNEST(quantity) quantity WITH OFFSET USING(OFFSET)
    JOIN UNNEST(price) price WITH OFFSET USING(OFFSET)  
)) rec

with output

Row id  code        quantity    price    
1   1   product1    10          2.99     
2   1   product2    20          1.99     
3   1   product3    30          0.99     
4   2   product4    60          3.99     
5   2   product5    50          4.99     
6   2   product6    40          5.99

How to associate multiple BigQuery array fields?

1 Answers