Google bigQuery split column

Question

I am using google bigQuery to analyze the amazon book review dataset. The dataset has a 'helpful' column which looks like this: 'helpful' :[0, 0] where the first element is a 'yes' vote and the second is a 'total' vote.

When I upload the data inn bigQuery, it splits the column into two rows because it does not support the array format. bigQuery screenshot. Using the 'apply series' method in python pandas I can split the helpful column into two columns- 'helpful vote' and 'total vote': jupyter notebook screenshot

If I write this query to do the same in BQ:

SELECT TA1.reviewerID, TA1.helpful AS yes, TA2.helpful AS total
FROM table_name as TA1

LEFT JOIN table_name as TA2
ON TA1.reviewerID = TA2.reviewerID and TA2.helpful != TA1.helpful
GROUP BY TA1.reviewerID

I get the following error: Error: (L1:27): Expression 'TA1.helpful' is not present in the GROUP BY list.

The same query works in my sqlite3. What am I doing wrong in BQ?

Thanks,

this seems like an interesting dataset, would you be able to share it? — Felipe Hoffa
Thanks! Now, if you have it loaded in BigQuery and it's open data, you could share the dataset publicly. It would also make much easier to answer questions like this :). twitter.com/felipehoffa/status/761635507080081408 — Felipe Hoffa

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-01-22T15:04:17

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  reviewerID, 
  helpful[OFFSET(0)] AS helpfulVote, 
  helpful[OFFSET(1)] AS totalVote 
FROM table_name

You can test it with dummy data as below

#standardSQL
WITH table_name AS (
  SELECT 'A' AS reviewerID, [0,0] AS helpful UNION ALL
  SELECT 'B' AS reviewerID, [0,2] AS helpful
)
SELECT 
  reviewerID, 
  helpful[OFFSET(0)] AS helpfulVote, 
  helpful[OFFSET(1)] AS totalVote 
FROM table_name

See more for Accessing Array Elements

In case if for some reason you are still using BigQuery Legacy SQL, you can use below version (meantime see Migrating from legacy SQL to Standard SQL as this is a preferred version to use)

#legacydSQL
SELECT 
  reviewerID,
  MIN(helpful) WITHIN RECORD AS helpfulVote,
  MAX(helpful) WITHIN RECORD AS totalVote
FROM table_name

The same query works in my sqlite3. What am I doing wrong in BQ?

And finally, if you want your original query to work/fixed - see below and read more about how GROUP BY works

#legacydSQL
SELECT 
  TA1.reviewerID as reviewerID, 
  MIN(TA1.helpful) AS yes, 
  MAX(TA2.helpful) AS total
FROM table_name   as TA1
LEFT JOIN table_name   as TA2
ON TA1.reviewerID = TA2.reviewerID -- and TA2.helpful != TA1.helpful
GROUP BY TA1.reviewerID

Google bigQuery split column

1 Answers