'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.

google-bigquery

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'BigQuery unnest to new columns instead of new rows

Sources

Related Questions