Is it possible to UNNEST an array in BigQuery so that the nested data in split into columns by a key value?

Question

Let's say I have some data in BigQuery which includes a nested array of objects like so:

{
    "name" : "Bob",
    "age": "24",
    "customFields": [
      {
        "index": "1",
        "value": "1.98"
      },
      {
        "index": "2",
        "value": "Nintendo"
      },
      {
        "index": "3",
        "value": "Yellow"
      }
    ]
}

I've only been able to unnest this data so that the "index" and "value" fields are columns:

+------+-----+-------+----------+
| name | age | index |  value   |
+------+-----+-------+----------+
| Bob  |  24 |     1 | 1.98     |
| Bob  |  24 |     2 | Nintendo |
| Bob  |  24 |     3 | Yellow   |
+------+-----+-------+----------+

In most cases this would be the desired output, but as the data I'm using refers to Google Analytics custom dimensions I require something a bit more complex. I'm trying to get the index value to be used in the name of the column the data appears in, like so:

+------+-----+---------+----------+---------+
| name | age | index_1 | index_2  | index_3 |
+------+-----+---------+----------+---------+
| Bob  |  24 |    1.98 | Nintendo | Yellow  |
+------+-----+---------+----------+---------+

Is this possible? What would be the SQL query required to generate this output? It should use the "index" value in he column name, as the output won't be in the ordered "1,2,3,..." all the time.

David David · Accepted Answer · 2019-05-20T17:32:44

What you are describing is often referred to as a pivot table - a transformation where values are used as columns. SQL doesn't generally support this as SQL is designed around the concept of having a fixed schema while pivot table requires dynamic schemas.

However if you have a fixed set of index columns you can emulate it with something like:

SELECT
  name,
  age,
  ARRAY(SELECT value FROM UNNEST(customFields) WHERE index="1")[SAFE_OFFSET(0)] AS index_1,
  ARRAY(SELECT value FROM UNNEST(customFields) WHERE index="2")[SAFE_OFFSET(0)] AS index_2,
  ARRAY(SELECT value FROM UNNEST(customFields) WHERE index="3")[SAFE_OFFSET(0)] AS index_3
FROM your_table;

What this does is specifically define columns for each index that picks out the right values from the customFields array.

Is it possible to UNNEST an array in BigQuery so that the nested data in split into columns by a key value?

1 Answers