CODEWITHSUNDEEP
google-bigquery
arunK
arunK

Reputation: 418

Transforming Arrays into Columns in BQ

Original Question - Transform Array into columns in BigQuery

Solution suggested in the original question works well when you want to extract the same information from the array elements. But in my case, the information that I want to extract from each array element can be different. For example- If you see the original question, the 3rd Array element we have doesn't have jsonPayload but instead it has nameValuePairs in it. If I use pivot there then unnecessary fields get created. How to avoid them, I know we can use EXCEPT but I don't think that is a good solution because If I have to choose different elements from each array element, it would be really a mess. As I can have 10+ payloads in the payloads array.

SQL -

select * from (
  select 
    json_value(payload,'$.pool') as pool,
    json_value(payloadArr, '$.name') as name, 
    json_value(payloadArr, '$.fullpath') as fullPath,
    json_value(payloadArr, '$.jsonPayload.body') as payload,
    json_value(payloadArr, '$.nameValuePairs.data.one') as nv, 
  from  table t
  , unnest(json_extract_array(payload, '$.payloads')) payloadArr
)
pivot (any_value(fullPath) as fullPath , any_value(payload) as payload,  any_value(nv) as nv for name in ('request', 'response', 'attributes') )

enter image description here

Upvotes: 0

Views: 262

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Use below

select * from (
  select 
    json_value(payload,'$.pool') as pool,
    json_value(payloadArr, '$.name') as name, 
    json_value(payloadArr, '$.fullpath') as fullPath,
    coalesce(
      json_value(payloadArr, '$.jsonPayload.body'),
      json_value(payloadArr, '$.nameValuePairs.data.one')
    ) as payload, 
  from  table t
  , unnest(json_extract_array(payload, '$.payloads')) payloadArr
)
pivot (any_value(fullPath) as fullPath , any_value(payload) as payload for name in ('request', 'response', 'attributes') )

with output

enter image description here

Upvotes: 1

Related Questions