How do i filter rows in bigquery, where a nested repeated field contains certain value?

Question

Lets assume the nested repeated field is Experience.Company, Experience.Months. Now if the Experience record contains , say, "GE" in Experience.Company, i want to skip the entire record.

The query i tried is something of this sort:

select name, location from table_name where Experience.Company != "GE".

This obviously doesn't work, since i get the record for other values.

I tried OMIT IF, with the same result.

Anything else I could try?

score 3 · Answer 1 · answered Jul 28 '17 at 13:29

3

Below is for BigQuery Standard SQL

#standardSQL
WITH `table_name` AS (
  SELECT 1 AS id, 'John' AS Name, 'LA' AS Location, [STRUCT<Company STRING, Months INT64>('Google', 24), ('Apple', 36)] AS Experience UNION ALL
  SELECT 2, 'Nick', 'SF', [STRUCT<Company STRING, Months INT64>('GE', 12), ('Microsoft', 48)] AS Experience UNION ALL
  SELECT 3, 'Mike', 'LV', [STRUCT<Company STRING, Months INT64>('Facebook', 24), ('Cloudera', 36)] AS Experience 
)
SELECT name, location FROM `table_name`
WHERE NOT EXISTS (SELECT 1 FROM UNNEST(Experience) WHERE Company = 'GE')

answered Jul 28 '17 at 13:29

Mikhail Berlyant

165,386
8
154
230

Thank you Mikhail.I need to use legacy SQL. I finally ended up using the solution explained here: https://stackoverflow.com/questions/38277110/bigquery-repeated-field-contains?rq=1 – Vishakha Kulkarni Jul 30 '17 at 08:06
make sense. consider voting that answer then :o) – Mikhail Berlyant Jul 30 '17 at 14:49

score 0 · Accepted Answer · answered Jul 30 '17 at 08:09

0

I refered to

BigQuery REPEATED field contains

so the query looks like:

Select column1, column2, 
sum(experience.company contains "GE") within record as matches 
from [mytable] 
having matches = 0

answered Jul 30 '17 at 08:09

Vishakha Kulkarni

121
2
11

How do i filter rows in bigquery, where a nested repeated field contains certain value?

2 Answers2