Flatten BigQuery nested field contents into new columns instead of rows
<p>I have some BigQuery data in the following format:</p>

<pre><code>"thing": [
  {
    "name": "gameLost",
    "params": [
      {
        "key": "total_games",
        "val": {
          "str_val": "3",
          "int_val": null
        }
      },
      {
        "key": "games_won",
        "val": {
          "str_val": "2",
          "int_val": null
        }
      },
      {
        "key": "game_time",
        "val": {
          "str_val": "44",
          "int_val": null
        }
      }
    ],
    "dt_a": "1470625311138000",
    "dt_b": "1470620345566000"
  }
</code></pre>

<p>I'm aware of the FLATTEN() function that will result in an output of 3 rows like so:</p>

<pre><code>+------------+------------------+------------------+--------------------+--------------------------+--------------------------+
| thing.name | thing.dt_a       | event_dim.dt_b   | thing.params.key   | thing.params.val.str_val | thing.params.val.int_val |
+------------+------------------+------------------+--------------------+--------------------------+--------------------------+
| gameLost   | 1470625311138000 | 1470620345566000 | total_games_played | 3                        | null                     |
|            |                  |                  |                    |                          |                          |
| gameLost   | 1470625311138000 | 1470620345566000 | games_won          | 2                        | null                     |
|            |                  |                  |                    |                          |                          |
| gameLost   | 1470625311138000 | 1470620345566000 | game_time          | 44                       | null                     |
+------------+------------------+------------------+--------------------+--------------------------+--------------------------+
</code></pre>

<p>where the higher level keys/values get repeated into new rows for each deeper level object.</p>

<p>However, I need to output the deeper key/values as entirely new columns, and not repeat fields so the results would look like this:</p>

<pre><code>+------------+------------------+------------------+--------------------+-----------+-----------+
| thing.name | thing.dt_a       | event_dim.dt_b   | total_games_played | games_won | game_time |
+------------+------------------+------------------+--------------------+-----------+-----------+
| gameLost   | 1470625311138000 | 1470620345566000 | 3                  | 2         | 44        |
+------------+------------------+------------------+--------------------+-----------+-----------+
</code></pre>

<p>How can I do this? <br>Thanks!</p>