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

Question

I have Bigquery table name_fruits like this...

| name | fruits |
|------|--------|
| aaaa | [apple, orange, grape] |
| bbbb | [apple, orange] |
| cccc | [orange, grape] |
| dddd | [orange] |
| eeee | [orange, peach] |

I am trying to select names that who has fruits both apple and orange. Which means I want to select aaaa and bbbb.

The query needs to be dynamic. Can't hardcode apple and orange in the query.

Is there any solution to select them?

SELECT name
FROM name_fruits, UNNEST(fruits) fruit
WHERE fruit IN UNNEST(@find_fruits) # IN query can only "OR" search condition
AND ARRAY_LENGTH(fruits) >= ARRAY_LENGTH(@find_fruits)

# @find_fruits = ["apple", "orange"]
#
# => aaaa, bbbb, cccc, eeee
# I want => aaaa, bbbb

Gordon Linoff · Accepted Answer · 2020-09-01 10:51:48Z

2

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);

answered Sep 1, 2020 at 10:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Soumendra Mishra · Accepted Answer · 2020-09-01 10:39:32Z

0

You can try this:

WITH
  `project.dataset.table` AS (
  SELECT 'aaaa' AS name, ['apple', 'orange', 'grape'] AS fruits
  UNION ALL
  SELECT 'bbbb' AS name, ['apple', 'orange'] AS fruits
  UNION ALL
  SELECT 'cccc' AS name, ['orange', 'grape'] AS fruits
  UNION ALL
  SELECT 'dddd' AS name, ['orange'] AS fruits
  UNION ALL
  SELECT 'eeee' AS name, ['orange', 'peach'] AS fruits )
SELECT name
FROM `project.dataset.table` 
WHERE (
  SELECT COUNT(1) 
  FROM UNNEST(fruits) AS fruit 
  WHERE fruit IN ('apple', 'orange')
) >=2

answered Sep 1, 2020 at 10:39

Soumendra Mishra

3,7131 gold badge18 silver badges40 bronze badges

Comments

Mikhail Berlyant · Accepted Answer · 2020-09-02 03:46:17Z

Below is for BigQuery Standard SQL and using Scripting functionality of BigQuery

#standardSQL 
DECLARE search ARRAY<STRING>;
SET search = ['apple', 'orange'];

CREATE TEMP FUNCTION checkFruits(fruits ARRAY<STRING>, search ARRAY<STRING>) AS (
  ARRAY_LENGTH(search) = (
    SELECT COUNT(DISTINCT fruit) 
    FROM UNNEST(fruits) AS fruit 
    WHERE fruit IN UNNEST(search)
  )
);
SELECT name
FROM `project.dataset.table`
WHERE checkFruits(fruits, search)

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

Row name     
1   aaaa     
2   bbbb

In case you you expect list of names as output - you can use below instead of SELECT name

SELECT STRING_AGG(name, ', ') names

in this case - output is

Row names    
1   aaaa, bbbb

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related