Bigquery filter two array columns-CodePudding

I have a two columns that have been joined from two tables that i'm trying to filter like so:

row app1       app2
1   [app,app1] []
2   []         [app]
3   [app1]     [app]
4   []         []

now im trying to retrieve all rows where either app1 or app2 contain 'app'. I have come up with the following, however it will only return one row, which is row 3. The rows where either column is empty will not be returned.

    select * from table
    where exists(SELECT * FROM UNNEST(array(select app1 from table)) AS s, 
    UNNEST(array(select app2 from table)) AS d WHERE regexp_contains(format('%t',(s,d)), 
    'app'))

This only returns row 3, what I am expecting is rows 1,2,3 to be returned.

Any help with biqquery can be of great help

CodePudding user response：

... filter two array columns ... - ... retrieve all rows where either app1 or app2 contain 'app'

When contain references arrays rather then strings - I am assuming your are looking for array containing app as an element , not as a part of string!!
If my this is true - consider below

select *
from your_table
where 'app' in unnest(app1 || app2)

if applied to sample data in your question - output is

CodePudding user response：

Simply you can do with below query:

SELECT * 
  FROM sample
 WHERE REGEXP_CONTAINS(FORMAT('%t', ARRAY_CONCAT(app1, app2)), r'app');

output: