How to Find Elements in an Array in Bigquery

How do I find elements in an array in BigQuery

#standardSQL
SELECT ip
FROM yourTable 
WHERE (
  SELECT COUNT(1) 
  FROM UNNEST(cookie) AS pair 
  WHERE pair IN (('grape', 'purple'),  ('orange', 'orange'))
) >= 2

you can test it with below dummy data

#standardSQL
WITH yourTable AS (
  SELECT '192.168.1.1' AS ip, [('apple', 'red'), ('orange', 'orange'), ('grape', 'purple')] AS cookie UNION ALL
  SELECT '192.168.1.2', [('abc', 'xyz')]
)
SELECT ip
FROM yourTable 
WHERE (
  SELECT COUNT(1) 
  FROM UNNEST(cookie) AS pair 
  WHERE pair IN (('grape', 'purple'),  ('orange', 'orange'))
) >= 2

In case if you need output ip if at least one pair is in array - you need to change >= 2 to >=1 in WHERE clause

How to find the index of an element in an Array in BigQuery

You can use OFFSET for this, as in below example

select element, date, status
from your_table t, 
t.status status with offset as offset1
left join t.entered_date date with offset as offset2
on offset1 + 1 = offset2
where status = 'status2'    

if applied to sample data in your question - output is

Hope you can adopt above technique to whatever exactly use case you have

BigQuery Arrays - check if Array contains specific values

Assuming you are looking for rows where ALL elements in match array [20, 30] are found in target array (some_numbers). Also assuming no duplicate numbers in both (match and target) arrays

select id, some_numbers
from sequences a,
unnest([struct([20, 30] as match)]) b
where (
  select count(1) = array_length(match) 
  from a.some_numbers num
  join b.match num
  using(num)
)

Check to see if Multiple Elements in BigQuery ARRAY

You can use below version

CASE (SELECT COUNT(1) FROM UNNEST(myArray) el WHERE el IN ("7", "8")) > 0 
  WHEN TRUE THEN 'value is in array'
  ELSE 'value is not in array' 
END result    

Obviously you can use simple

CASE "7" in UNNEST(myArray) or "8" in UNNEST(myArray) WHEN ...     

but this will become too heavy if you have more than just two strings to lookup, while the first option does not have this "problem"

Finally you can use below "streamlined" version with UDF

#standardSQL
CREATE TEMP FUNCTION isInArray(arr1 ANY TYPE, arr2 ANY TYPE) AS (
  (SELECT COUNT(1) FROM UNNEST(arr1) el JOIN UNNEST(arr2) el USING(el)) > 0 
SELECT *, 
  CASE isInArray(myArray, ["7", "8"])
    WHEN TRUE THEN 'value is in array'
    ELSE 'value is not in array' 
  END result  
FROM `project.dataset.table` 

Bigquery select records where array column in array value by 'And' condition

If I understand correctly, you want to do this with variables. Hence:

SELECT nf.*
FROM name_fruits nf
WHERE (SELECT COUNT(1) 
       FROM UNNEST(nf.fruits) fruit JOIN
            UNNEST(@find_fruits) ff
            ON ff = fruit
      ) >= (SELECT COUNT(*) FROM UNNEST(@find_fruits);

Bigquery: Find the index at which a specified element is first found within an array

Below is for BigQuery Standard SQL

#standardSQL
select 
  ( select offset 
    from unnest(metric) m with offset 
    where m = 'b'
  ) index, 
  ( select v
    from unnest(metric) m with offset
    join unnest(value) v with offset
    using(offset)
    where m = 'b'
  ) b
from `project.dataset.table` 

If to apply to sample data from your question - output is

Another option (obviously with the same result):

#standardSQL
select index, value[offset(index)] value
from (
  select *,
    ( select offset 
      from unnest(metric) m with offset 
      where m = 'b'
    ) index
  from `project.dataset.table` 
)

Filter elements from array in bigquery

You can filter it with REGEXP. It may help to filter multiple array columns or huge tables

WITH arrays as (
    SELECT ["apple", "of", "the", "tree"] array_col, 1  label
    UNION ALL (SELECT ["boy", "of", "the", "streets"] array_col, 2 label)
)
SELECT JSON_VALUE_ARRAY(REGEXP_REPLACE(TO_JSON_STRING(array_col), r'"(of|the)",?',''), '$') array_col, label  
FROM arrays

How can I get the last element of an array? SQL Bigquery

To get last element in array you can use below approach

select array_reverse(your_array)[offset(0)]   

I'm unsure how to combine that in my code

select  type, array_reverse(array(
    select trim(val) 
      from unnest(split(trim(payload, '[]'))) val
  ))[offset(0)] 
from `githubarchive.day.20210606`
where type = 'MemberEvent' 

---

Related Topics