I want to know a similar function for Array_contains in BigQuery. I am trying to get results for multiple values present in Array of Struct field

Question

I have a Array of Struct field containing values like- field_name= [1,2,3,4]

I want to identify rows where field_name NOT contains values 2 OR 3 in BigQuery

Sample Data: Assume I have 4 rows of data:
row_id = 1 , field_name = [1,2,3,4]
row_id = 2 , field_name = [1,2]
row_id = 3 , field_name = [3,4]
row_id = 4 , field_name = [4]

What I want- Expected Result: row_id = 4 , field_name = [4]
I tried with UNNEST and EXISTS, UNNEST gives output as row_id = 1, row_id = 2, row_id = 3 & row_id = 4

Answer 1

Consider below approach

select * from your_table t 
where not exists (
  select value 
  from t.field_name as value
  where value in (2,3)
)           

if applied to sample data in your question - output is

if you would use where value in (1,2) - output were