CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
David542
David542

Reputation: 110163

Bigquery struct introspection

Is there a way to get the element types of a struct? For example something along the lines of:

SELECT #TYPE(structField.y)
SELECT #TYPE(structField)
...etc

Is that possible to do? The closest I can find is via the query editor and the web call it makes to validate a query:

enter image description here

Upvotes: 0

Views: 337

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

As I mentioned already in comments - one of the option is to mimic same very Dry Run call with query built in such a way that it will fail with exact error message that will give you the info you are looking for. Obviously this assumes your use case can be implemented in whatever scripting language you prefer. Should be relatively easy to do.

Meantime, I was looking for making this within the SQL Query.
Below is the example of another option.
It is limited to below types, which might fit or not into your particular use case

object, array, string, number, boolean, null

So example is

select
  s.birthdate, json_type(to_json(s.birthdate)), 
  s.country, json_type(to_json(s.country)),
  s.age, json_type(to_json(s.age)),
  s.weight, json_type(to_json(s.weight)),
  s.is_this, json_type(to_json(s.is_this)),
from (
  select struct(date '2022-01-01' as birthdate, 'UA' as country, 1 as age, 2.5 as weight, true as is_this) s
)

with output

enter image description here

Upvotes: 1

Scott B
Scott B

Reputation: 2964

You can try the below approach.

SELECT COLUMN_NAME, DATA_TYPE
FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE TABLE_NAME = 'your-table-name'
AND COLUMN_NAME = 'your-struct-column-name'
ORDER BY ORDINAL_POSITION

You can check this documentation for more details using INFORMATION_SCHEMA for BigQuery.

Below is the screenshot of my testing.

DATA: enter image description here

RESULT USING THE ABOVE SYNTAX: enter image description here

Upvotes: 1

Related Questions