BigQuery Filter Rows by Array Entry

Question

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".

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
)

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.

SELECT *, (SELECT MAX(IF(appEntry LIKE "%nativeapp%", 1, 0 )) FROM TESTTABLE, UNNEST(application) as appEntry) as isNativeSession FROM TESTTABLE

So the result should be:

Mikhail Berlyant · Accepted Answer · 2019-09-23 17:01:30Z

1

Below is for BigQuery Standard SQL

#standardSQL
SELECT *, 
  (SELECT SIGN(COUNT(1)) FROM UNNEST(application) value WHERE value = 'nativeapp') AS isNativeSession,
  (SELECT CAST(STRING_AGG(DISTINCT value) = 'nativeapp' AS INT64) FROM UNNEST(application) value) AS isNativeOnlySession 
FROM TESTTABLE

with result

answered Sep 23, 2019 at 17:01

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

BigQuery Filter Rows by Array Entry

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related