CODEWITHSUNDEEP
google-bigquery
David M Smith
David M Smith

Reputation: 2332

Splitting a string column in BigQuery

Let's say I have a table in BigQuery containing 2 columns. The first column represents a name, and the second is a delimited list of values, of arbitrary length. Example:

Name | Scores
-----+-------
Bob  |10;20;20
Sue  |14;12;19;90
Joe  |30;15

I want to transform into columns where the first is the name, and the second is a single score value, like so:

Name,Score
Bob,10
Bob,20
Bob,20
Sue,14
Sue,12
Sue,19
Sue,90
Joe,30
Joe,15

Can this be done in BigQuery alone?

Upvotes: 26

Views: 56012

Answers (4)

Breno Marcolino
Breno Marcolino

Reputation: 1

Let's call your example table "FACT_SCORES".

Try this:

WITH SCORE_LIST AS (
SELECT
  FS.NAME
  ,FS.SCORES
  ,SPLIT(FS.SCORES,';') AS SCORES_ARRAY
FROM FACT_SCORES FS
)

SELECT
  SL.NAME
  ,SL.SCORES
  ,SCORE
FROM SCORE_LIST SL
CROSS JOIN UNNEST(SL.SCORES_ARRAY) AS SCORE

What you we're trying to do is called flattening arrays.

I first transformed your list into an array, and then flattened it.

Google has some very good documentation on working with arrays: https://cloud.google.com/bigquery/docs/arrays

And a specific part about converting elements in an array to rows in a table: https://cloud.google.com/bigquery/docs/arrays#flattening_arrays

Upvotes: 0

az3
az3

Reputation: 3629

Trying to rewrite Elad Ben Akoune's answer in Standart SQL, the query becomes like this;

WITH name_score AS (
SELECT Name, split(Scores,';') AS Score
FROM (
      (SELECT * FROM (SELECT 'Bob' AS Name ,'10;20;20' AS Scores)) 
      UNION ALL 
      (SELECT * FROM (SELECT 'Sue' AS Name ,'14;12;19;90' AS Scores))
      UNION ALL
      (SELECT * FROM (SELECT 'Joe' AS Name ,'30;15' AS Scores))
)) 
SELECT name, score
FROM name_score
CROSS JOIN UNNEST(name_score.score) AS score;

And this outputs;

+------+-------+
| name | score |
+------+-------+
| Bob  | 10    |
| Bob  | 20    |
| Bob  | 20    |
| Sue  | 14    |
| Sue  | 12    |
| Sue  | 19    |
| Sue  | 90    |
| Joe  | 30    |
| Joe  | 15    |
+------+-------+

Upvotes: 19

Elad Ben Akoune
Elad Ben Akoune

Reputation: 181

If someone is still looking for an answer

select Name,split(Scores,';') as Score
from (
      # replace the inner custome select with your source table
      select *
      from 
      (select 'Bob' as Name ,'10;20;20' as Scores),
      (select 'Sue' as Name ,'14;12;19;90' as Scores),
      (select 'Joe' as Name ,'30;15' as Scores)
);

Upvotes: 13

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Good news everyone! BigQuery can now SPLIT()!

Look at "find all two word phrases that appear in more than one row in a dataset".

There is no current way to split() a value in BigQuery to generate multiple rows from a string, but you could use a regular expression to look for the commas and find the first value. Then run a similar query to find the 2nd value, and so on. They can all be merged into only one query, using the pattern presented in the above example (UNION through commas).

Upvotes: 20

Related Questions