CODEWITHSUNDEEP
google-bigquery
Kali Muthu
Kali Muthu

Reputation: 33

Join with array fields in BigQuery

We have tables Table1 and Table2

Table 1

ID name  subject      
1  xxx   physics   
         maths
         chemistry
2  yyy   physics   
         maths
         chemistry
Table 2

Id   Marks
1    70
     67
     88
2    90
     99
     89

We need to join the above two tables like this format with condition Table1.Id=Table2.Id,

Id    name   subject       Marks
1     xxx    physics       70
             maths         67
             chemistry     88
2     yyy    physics       90
             maths         99
             chemistry     89

Upvotes: 1

Views: 609

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

#standardSQL
WITH `project.dataset.table1` AS (
  SELECT 1 id, 'xxx' name, ['physics', 'maths', 'chemistry'] subject UNION ALL
  SELECT 2, 'yyy', ['physics', 'maths', 'chemistry'] 
), `project.dataset.table2` AS (
  SELECT 1 id, [70, 67, 88] marks UNION ALL
  SELECT 2, [90, 99, 89] 
)
SELECT *
FROM `project.dataset.table1` t1
JOIN `project.dataset.table2` t2
USING(id)

with result 

Row id  name    subject     marks    
1   1   xxx     physics     70   
                maths       67   
                chemistry   88   
2   2   yyy     physics     90   
                maths       99   
                chemistry   89

Upvotes: 1

Related Questions