Sql Google BigQuery：UNNEST，每个不同的键都变成一列

使用BigQuery PIVOT和EXECUTE IMMEDIATE可以相对容易地完成，如下例所示

create temp table temp as (
  select t.* except(payloadKV, metaKV), replace(key, '.', '_') key, value
  from your_table t, unnest(payloadKV)
  union all
  select t.* except(payloadKV, metaKV), replace(key, '.', '_') key, value
  from your_table t, unnest(metaKV)
);

execute immediate (select '''
  select * from temp pivot (any_value(value) for key in (''' || 
  (select string_agg("'" || key || "'", ',' order by key) from (select distinct key from temp))
  || '''))
''')           

如果应用于样本数据(类似于)你的问题

select '2022-03-23 02:34:41' ingestTimestamp, [
    struct('payload.Area' as key, 'MC1' as value), ('payload.Cell', '00101'), ('payload.Department', '07521')] payloadKV, [
    struct('metadata.Area' as key, 'MC' as value),('metadata.Cell', '0010'), ('metadata.Department', '0752')] metaKV
union all
select '2022-03-24 02:34:41' ingestTimestamp, [
    struct('payload.Area' as key, 'MC2' as value), ('payload.Cell', '00102'), ('payload.Department', '07522')] payloadKV, [
    struct('metadata.Area' as key, 'MC3' as value),('metadata.Cell', '00103'), ('metadata.Department', '07523')] metaKV

输出是