Google Cloud - BigQuery - Transposing 2 String Columns within a Struct

Question

Good Morning Everyone,

New to BigQuery and am stumbling over something that I find quite intuitive in other BI languages I have used in the past, namely transposing keys and values so that the key becomes the column name and the value becomes the value associated with the column.

I have looked at a number of examples online and when I work through them I am presented with a multitude of various errors that I am unable to resolve, because I simply don't have enough experience.

For those inevitably questioning why I may want to do this, it is a business requirement that I am obliged to fulfill, however if you have any thoughts on better ways to approach this, I am more than happy to take this advice and appreciate it.

**The Json:**

[
  {
    "resultsMap": {
      "map": {
        "item": [
          {
            "value": "2021-03-02-12.16.28.012279",
            "key": "TIMESTAMP"
          },
          {
            "value": "1",
            "key": "CONTROL_KEY"
          },
          {
            "value": "123465789",
            "key": "LOGGING_KEY"
          }
        ],
        "key": "5049"
      }
    }
  }
]

What I have

resultsMap.map.key	resultsMap.map.item.value	resultsMap.map.item.key
5049	TIMESTAMP	2021-03-02-12.16.28.012279
	CONTROL_KEY	1
	LOGGING_KEY	123456789

What I want

resultsMap.map.key	TIMESTAMP	CONTROL_KEY	LOGGING_KEY
5049	2021-03-02-12.16.28.012279	1	123456789

Thank you very much in advance for your help.

Regards, Scott

user_13473525 · Answer

We have have to select from array manually for such cases:

with mytable as (
  select struct( struct(5049 as key, [struct("2021-03-02-12.16.28.012279" as value, "TIMESTAMP" as key), struct("1" as value, "CONTROL_KEY" as key), struct("123465789" as value, "LOGGING_KEY" as key)] as item) as map) as resultsMap
)
select
  resultsMap.map.key,
  (select value from unnest(resultsMap.map.item) where key = 'TIMESTAMP') as TIMESTAMP,
  (select value from unnest(resultsMap.map.item) where key = 'CONTROL_KEY') as CONTROL_KEY,
  (select value from unnest(resultsMap.map.item) where key = 'LOGGING_KEY') as LOGGING_KEY
from mytable

user_5221944 · Answer

Consider also below (assuming all to extract keys are present):

select resultsMap.map.key,
  kv[offset(0)] as TIMESTAMP, 
  kv[offset(1)] as CONTROL_KEY, 
  kv[offset(2)] as LOGGING_KEY, 
from `project.dataset.table` t, 
unnest([(
  select as struct array_agg(value 
    order by case key 
      when 'TIMESTAMP' then 0 
      when 'CONTROL_KEY' then 1 
      when 'LOGGING_KEY' then 2 
    end) as kv 
  from t.resultsMap.map.item 
)])

and also further refactored version:

select resultsMap.map.key,
  kv[offset(2)] as TIMESTAMP, 
  kv[offset(0)] as CONTROL_KEY, 
  kv[offset(1)] as LOGGING_KEY, 
from `project.dataset.table` t,
unnest([(
  select as struct array_agg(value 
    order by range_bucket(key, ['CONTROL_KEY','LOGGING_KEY','TIMESTAMP'])
  ) as kv 
  from t.resultsMap.map.item 
)])

if applied to sample data in your question - output is

Google Cloud - BigQuery - Transposing 2 String Columns within a Struct

2 Answers