CODEWITHSUNDEEP
google-bigquery
Jean Picard
Jean Picard

Reputation: 81

BigQuery unnest to new columns instead of new rows

I have a BigQuery table with nested and repeated columns that I'm having trouble unnesting to columns instead of rows.

This is what it the table looks like:

  {
    "dateTime": "Tue Mar 01 2022 11:11:11 GMT-0800 (Pacific Standard Time)",
    "Field1": "123456",
    "Field2": true,
    "Matches": [
      {
        "ID": "abc123",
        "FieldA": "asdfljadsf",
        "FieldB": "0.99"
      },
      {
        "ID": "def456",
        "FieldA": "sdgfhgdkghj",
        "FieldB": "0.92"
      },
      {
        "ID": "ghi789",
        "FieldA": "tfgjyrtjy",
        "FieldB": "0.64"
      }
    ]
  },

What I want is to return a table with each one of the nested fields as an individual column so I have a clean dataframe to work with:

  {
    "dateTime": "Tue Mar 01 2022 11:11:11 GMT-0800 (Pacific Standard Time)",
    "Field1": "123456",
    "Field2": true,
    "Matches_1_ID": "abc123",
    "Matches_1_FieldA": "asdfljadsf",
    "Matches_1_FieldB": "0.99", 
    "Matches_2_ID": "def456",
    "Matches_2_FieldA": "sdgfhgdkghj",
    "Matches_2_FieldB": "0.92",
    "Matches_3_ID": "ghi789",
    "Matches_3_FieldA": "tfgjyrtjy",
    "Matches_3_FieldB": "0.64"
  },

I tried using UNNEST as below, however it creates new rows with only one set of additional columns, so not what I'm looking for.

SELECT * 
FROM table,
UNNEST(Matches) as items

Any solution for this? Thank you in advance.

Upvotes: 0

Views: 1037

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Consider below approach

select * from (
  select t.* except(Matches), match.*, offset + 1 as offset 
  from your_table t, t.Matches match with offset
)
pivot (min(ID) as id, min(FieldA) as FieldA, min(FieldB) as FieldB for offset in (1,2,3))

If applied to sample data in y our question - output is

enter image description here

if there is are no matches, that entire row is not included in the output table, whereas I want to keep that record with the fields it does have. How can I modify to keep all records?

use left join instead as in below

select * from (
  select t.* except(Matches), match.*, offset + 1 as offset 
  from your_table t left join t.Matches match with offset
)
pivot (min(ID) as id, min(FieldA) as FieldA, min(FieldB) as FieldB for offset in (1,2,3))

Upvotes: 1

Related Questions