title: BigQuery Filter Rows by Array Entry
body: <p>I need to create columns, derived from array values within an entry. The following example table has 4 entries which are called "sessions" and each session has multiple application-values that were visited (either onlineshop or nativeapp). In the derived column I want to have "1" value if in this session has at least one entry of "nativeapp". In the second column I need to have "1" if all entries are "nativeapp". Else the value should be "0".</p>
  
  <pre><code>WITH TESTTABLE AS (
    SELECT 'session A' AS session, SPLIT("onlineshop,onlineshop") AS application, 8 AS point UNION ALL
    SELECT 'session B' AS session, SPLIT("onlineshop,nativeapp") AS application, 9 AS point UNION ALL
    SELECT 'session C' AS session, SPLIT("onlineshop,nativeapp") AS application, 5 AS point UNION ALL
    SELECT 'session D' AS session, SPLIT("nativeapp") AS application, 4 AS point
  ) 
  </code></pre>
  
  <p>My attempt was to make a subquery "select ..." which filters the entries... however I'd than need to have the subquery the reference to its entry, but actually this only is a plump "1" if any entry within all the table fits the subquery.</p>
  
  <pre><code>SELECT *, (SELECT MAX(IF(appEntry LIKE "%nativeapp%", 1, 0 )) FROM TESTTABLE, UNNEST(application) as appEntry) as isNativeSession FROM TESTTABLE 
  </code></pre>
  
  <p>So the result should be:</p>
  
  <p><a href="https://i.stack.imgur.com/FfXFo.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FfXFo.jpg" alt="enter image description here"></a></p>