How to break down a string into separate lines in BigQuery?

Question

My BigQuery table contains a String column:

WITH
  food_thoughts AS (
    SELECT 1 as id, 'I want a kebab' AS thought UNION ALL
    SELECT 2, 'Pizza is delicious' UNION ALL
    SELECT 3, 'All you can eat French fries!'
  )
SELECT * FROM food_thoughts

id	thought
1	I want a kebab
2	Pizza is delicious
3	All you can eat French fries!

I want to isolate each word of each sentence into a dedicated cell and match it to the other columns. How can I achieve this using a BigQuery command?

The expected output is:

id	word
1	I
1	want
1	a
1	kebab
2	Pizza
2	is
2	...
3	fries!

Answer 1

You can try the following BigQuery code:

SELECT food_thoughts.id, word
FROM food_thoughts, unnest(split(thought, ' ')) as word

1. split your String into an Array of words with the split function
2. create a new table with the unnest FROM operator (documentation here) and an alias
3. refer to this alias to gather all informations needed in the SELECT statement