CODEWITHSUNDEEP
sqlgoogle-bigquery
David Beck
David Beck

Reputation: 975

Big Query Record split into multiple columns

I have a table that looks like: 

text           |  STRING
concepts       |  RECORD
concepts.name  |  STRING
[...]

So one row could look like this:

"This is a text about BigQuery and how to split records into columns. "
SQL
BigQuery
Questions

I would like to transform that into: 

text, concepts_1, concepts_2, concepts_3 // The names are not important
"This is a text about BigQuery and how to split records into columns. ",SQL,BigQuery,Questions

The number of concepts in each row vary.

EDIT:

This would also work:

text, concepts
"This is a text about BigQuery and how to split records into columns. ","SQL,BigQuery,Questions"

Upvotes: 0

Views: 3407

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

Below is for BigQuery Standard SQL

If comma separated list is fine with you - consider below shortcut versions 

#standardSQL
SELECT identifier, 
  (SELECT STRING_AGG(name, ', ') FROM UNNEST(concepts)) AS conceptName
FROM `project.dataset.articles`

and 

#standardSQL
SELECT identifier, 
  (SELECT STRING_AGG(name, ', ') FROM articles.concepts) AS conceptName
FROM `project.dataset.articles` articles

Both above versions return output like below 

Row identifier  conceptName  
1   1           SQL, BigQuery, Questions     
2   2           xxx, yyy, zzz

As you can see - above versions are brief and compact and don't use extra grouping to array with then transforming it into string - as all this can be done in one simple shot

Upvotes: 1

F10
F10

Reputation: 2883

Try using this:

SELECT
  text,
  c.*
FROM
  `your_project.your_dataset.your_table`,
  UNNEST(
  concepts
    ) c

This will get the text column along with the unnested values from your RECORD column.

Hope it helps.

Upvotes: 0

David Beck
David Beck

Reputation: 975

This was the solution for me. But it only creates a comma-separated string. However, in my case, this was fine. 

SELECT articles.identifier, ARRAY_TO_STRING(ARRAY_AGG(concepts.name), ",") as 
conceptName
FROM `` articles, UNNEST(concepts) concepts
GROUP BY articles.identifier

Upvotes: 0

Related Questions