CODEWITHSUNDEEP
google-bigquery
tuemar29
tuemar29

Reputation: 248

How to get values from BigQuery nested Record type

I have a BQ table that has a column of type RECORD that looks like this:

tags
 |_ sub_tags

When I do:

SELECT tags.sub_tags FROM `MY_TABLE`

I get this:

Row sub_tags.array_element
1 :something
:something_else
what::the:hell
2 more_stuff
3 and_more_stuff

How do I get the value or check if :something exists in the STRUCT?

Upvotes: 0

Views: 1291

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173141

Use below simple approach

select *
from your_table
where contains_substr(tags.sub_tags, ':something')

if applied to sample data in your question - output is

enter image description here

While this solution applicable for your specific simple case - solution provided by Pratik can cover more complex scenarious

Upvotes: 1

Pratik Patil
Pratik Patil

Reputation: 832

See if these solutions help: given that you have a struct in your source table with tags.sub_tags .

WITH yourTable AS (
  SELECT struct(['one', 'two','three'] AS sub_tags) as tags 
  union all 
  SELECT struct(['one', 'four','three'] AS sub_tags) as tags 

) 
SELECT * FROM `yourTable` 
where 'two' in unnest(tags.sub_tags) 

SELECT * except(value) FROM `yourTable`, unnest(tags.sub_tags) as value 
where value = 'two'

These are some good reference for arrays: https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

Upvotes: 2

Related Questions