Bigquery select records where array column in array value by ‘And’ condition

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

Answer

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