All the values inside Struct to columns in big query

user_17220802 0
Viewed 1215

I am new to bigquery, i have a column 'device' which has nested struct properties looks like this

device

brand_name brand_id country
huawei 34j0 china
brand_name brand_id country
sony ds5g japan

we can convert those device properties to column by doing this `

SELECT
device.brandname 
device.brand_id 
device.country 
from table

( the structre of the data is STRUCT<brand_name STRING,brand_id STRING,country STRING>)

but the problem is we can not access every individual elements in device ( like device.brandname device.brand_id....etc) if properties have n number of keys, is there a way to loop all the keys in device column in one go, thank you

google-bigquery
2 Answers

is there a way to loop all the keys in device column in one go

Use below approach

select * except(device), device.*
from your_table

in case if device is the only column in your table - use below

select device.*
from your_table

with output

enter image description here

user_52219440

I am assuming you have a schema with "device" being a nested structure, something like... (the parent fields are just by way of example)

Schema: parent_schema
|- parent_schema_id: string
|- device_owner: string
|- ... other properties ...
+- device: record
|  |- brand_id: string
|  |- brand_name: string
|  |- country: string
:

Based on that assumption, you should be able to write a query like...

SELECT
    parent_schema_ia,
    device_owner,
    device.brand_id,
    device.brand_name,
    device.country
  FROM parent_schema;

You can also reference the device columns in WHERE clauses like...

SELECT
    parent_schema_ia,
    device_owner,
    device.brand_id,
    device.brand_name,
    device.country
  FROM parent_schema
 WHERE device.brand_name = 'huawei';

This is all presented nicely on the Google BigQuery help...

https://cloud.google.com/bigquery/docs/legacy-nested-repeated

Apologies, if my assumption is incorrect... I wasn't clear from your question what the wider structure looks like.

user_53543150
Related