CODEWITHSUNDEEP
google-bigquery
Sivaprasad
Sivaprasad

Reputation: 107

Querying REPEATED records in Bigquery and cloud datalab

I have a table EMP which has Empno, Name , Age as general fields.

EMP.Salary as RECORD with Repeated type with two fields Month & Amt

EmpNo: 1
Age : 25
Name : Alex

Salary.Month : Jan
Salary.Amt : 2000

Salary.Month : Feb
Salary.Amt : 3000

Salary.Month : Mar
Salary.Amt : 3500

Emp No: 2
Age : 26
Name : Teresa

Salary.Month : Jan
Salary.Amt : 1000

Salary.Month : Feb
Salary.Amt : 2000

Salary.Month : Mar
Salary.Amt : 3000

How can I use standard sql in Bigquery to fetch these repeated items ?

Regards, Siva

Upvotes: 5

Views: 8649

Answers (2)

Sivaprasad
Sivaprasad

Reputation: 107

SELECT EmpNo, Age, Name, 
  (SELECT SUM(Amt) FROM UNNEST(Salary)) Total
FROM `project.dataset.your_table`

This worked well.

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

I need the biqQuery for following output. EmpNo Name Month Salary 1 Alex Jan 2000 1 Alex Feb 3000 1 Alex Mar 3500 2 Teresa Jan 1000 2 Teresa Feb 2000 2 Teresa Feb 3000

Below is for BigQuery Standard SQL 

#standardSQL
SELECT EmpNo, Age, Name, s.*
FROM `project.dataset.your_table` t, UNNEST(Salary) s

You can test / play with it using dummy data from your question as below 

#standardSQL
WITH `project.dataset.your_table` AS (
  SELECT 1 EmpNo, 25 Age, 'Alex' Name, 
    [STRUCT<Month STRING, Amt INT64>('Jan', 2000), ('Feb', 3000), ('Mar', 3500)] Salary UNION ALL
  SELECT 2, 26, 'Teresa',
    [STRUCT<Month STRING, Amt INT64>('Jan', 1000), ('Feb', 2000), ('Mar', 3000)] 
)
SELECT EmpNo, Age, Name, s.*
FROM `project.dataset.your_table` t, UNNEST(Salary) s

and result is 

Row EmpNo   Age Name    Month   Amt  
1   1       25  Alex    Jan     2000     
2   1       25  Alex    Feb     3000     
3   1       25  Alex    Mar     3500     
4   2       26  Teresa  Jan     1000     
5   2       26  Teresa  Feb     2000     
6   2       26  Teresa  Mar     3000

I need the details of empno, name and sum of salary

In case if you need SUM of salary you can use below example 

#standardSQL
WITH `project.dataset.your_table` AS (
  SELECT 1 EmpNo, 25 Age, 'Alex' Name, 
    [STRUCT<Month STRING, Amt INT64>('Jan', 2000), ('Feb', 3000), ('Mar', 3500)] Salary UNION ALL
  SELECT 2, 26, 'Teresa',
    [STRUCT<Month STRING, Amt INT64>('Jan', 1000), ('Feb', 2000), ('Mar', 3000)] 
)
SELECT EmpNo, Age, Name, 
  (SELECT SUM(Amt) FROM UNNEST(Salary)) Total
FROM `project.dataset.your_table`

and result is 

Row EmpNo   Age Name    Total    
1   1       25  Alex    8500     
2   2       26  Teresa  6000

Upvotes: 7

Related Questions