Not much to suggest... Official docs suggest to use exists:
WHERE EXISTS (
SELECT *
FROM UNNEST(some_numbers) AS s
WHERE s IN (20,30)
);
Google
docs.cloud.google.com › bigquery › work with arrays
Work with arrays | BigQuery | Google Cloud Documentation
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value; /*----------------+ | contains_value | +----------------+ | true | +----------------*/ To return the rows of a table where the array column contains a specific value, filter the results of IN UNNEST using the WHERE clause.
Top answer 1 of 2
7
Not much to suggest... Official docs suggest to use exists:
WHERE EXISTS (
SELECT *
FROM UNNEST(some_numbers) AS s
WHERE s IN (20,30)
);
2 of 2
2
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)
)
Google
docs.cloud.google.com › bigquery › array functions
Array functions | BigQuery | Google Cloud Documentation
The ARRAY will contain one STRUCT for each row in the subquery, and each of these STRUCTs will contain a field for each column in that row.
How do you create an array from rows in BigQuery?
You can create an array from rows in BigQuery using the ARRAY_AGG() function. This function combines values from multiple rows into a single array.
hevodata.com
hevodata.com › home › learn › bigquery
BigQuery Array Functions and Examples Simplified | Hevo Data
What is the difference between array and STRUCT in BigQuery?
The main difference is that an array stores multiple values of the same type (like
numbers or strings), while a STRUCT can hold different types of data in a single field. An array is like a list, while a STRUCT is like a record containing various fields.
numbers or strings), while a STRUCT can hold different types of data in a single field. An array is like a list, while a STRUCT is like a record containing various fields.
hevodata.com
hevodata.com › home › learn › bigquery
BigQuery Array Functions and Examples Simplified | Hevo Data
Owox
owox.com › blog › articles › data › google bigquery
BigQuery Array Functions: 2025 Guide to Syntax, Usage & Examples
January 16, 2026 - This query creates an array of ... data collectively rather than as separate values. ARRAY_CONCAT is a BigQuery function that merges two or more arrays into a single array....
Medium
medium.com › @jamiekt › til-selecting-arrays-in-bigquery-to-be-inserted-into-repeated-columns-6dc451dc2858
TIL: SELECTing arrays in BigQuery to be INSERTed into repeated columns | by Jamie Thomson | Medium
October 22, 2022 - The ARRAY will contain one STRUCT for each row in the subquery, and each of these STRUCTs will contain a field for each column in that row. https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#array
CastorDoc
castordoc.com › how-to › how-to-use-array-contains-in-bigquery
How to use array contains in BigQuery?
Learn how to use array_contains in BigQuery to efficiently search and manipulate arrays.
Medium
medium.com › data-to-decision › how-and-why-to-use-arrays-in-bigquery-59ba141658a4
How and why to use arrays in BigQuery | by Ed Rushton | Data to Decision | Medium
January 6, 2023 - The array data type in BigQuery allows you to store a list of values in a single row in a table.
Google Skills
skills.google › focuses › 3696
Working with JSON, Arrays, and Structs in BigQuery | Google Skills
December 16, 2025 - Task 4. Query tables containing arrays · Task 5. Introduction to STRUCTs · Task 6. Practice with STRUCTs and arrays · Task 7. Lab question: STRUCT() Task 8. Lab question: Unpacking arrays with UNNEST( ) Task 9. Filter within array values · Congratulations! This content is not yet optimized for mobile devices. For the best experience, please visit us on a desktop computer using a link sent by email. BigQuery ...
Hevo
hevodata.com › home › learn › bigquery
BigQuery Array Functions and Examples Simplified | Hevo Data
January 9, 2026 - In order to determine whether a specific BigQuery Array contains one particular value, you can use the IN Operator along with the UNNEST Operator.
Count
count.co › sql-resources › bigquery-standard-sql › arrays-explained
Arrays Explained | BigQuery Standard SQL - Count
ARRAYs are their own Data Type in BigQuery. They can be comprised of any data type EXCEPT for ARRAYs. So no ARRAY of ARRAYs, which is a relief, honestly.
Yuichi Otsuka
yuichiotsuka.com › home › blog › bigquery unnest and working with arrays
BigQuery UNNEST and Working with Arrays - Yuichi Otsuka
June 10, 2024 - For Black and White, the array only contains two elements. This means that it’s okay to have different numbers of elements in each array in the same column. For The Void, we will see an empty array, which is indicated by a grayed-out area. With arrays, since we do not have to repeat some information, this results in reduced storage costs for BigQuery.
TutorialsPoint
tutorialspoint.com › bigquery › bigquery-array-data-type.htm
BigQuery - ARRAY Data Type
Unlike a STRUCT type, which is allowed to contain data of differing types, an ARRAY data type must contain elements of the same type. BigQuery will not label a column as an explicit ARRAY type.
The Digital Skye
thedigitalskye.com › 2021 › 01 › 21 › explore-arrays-and-structs-for-better-performance-in-google-bigquery
Explore Arrays and Structs for Better Query Performance in Google BigQuery – The Digital Skye
January 21, 2021 - All data resides within one single table; each record is nested to optimize storage yet expandable (with UNNEST) for high query performance. Therefore, in BigQuery, we say nested records are arrays of structs.
Datawise
datawise.dev › accessing-array-elements-in-bigquery
BigQuery Array Access: OFFSET, ORDINAL, and SAFE Variants
March 2, 2026 - So here's 3 ways we can access elements in a BigQuery array.
Google
docs.cloud.google.com › bigquery › string functions
String functions | BigQuery | Google Cloud Documentation
Returns an array of all substrings of value that match the re2 regular expression, regexp. Returns an empty array if there is no match. If the regular expression contains a capturing group ((...)), the function returns an array of substrings ...
Microsoft Learn
learn.microsoft.com › en-us › azure › cosmos-db › nosql › query › array-contains
ARRAY_CONTAINS - Query Language for Cosmos DB (in Azure and Fabric) | Microsoft Learn
August 22, 2024 - SELECT VALUE { containsItem: ARRAY_CONTAINS(["coats", "jackets", "sweatshirts"], "coats"), missingItem: ARRAY_CONTAINS(["coats", "jackets", "sweatshirts"], "hoodies"), containsFullMatchObject: ARRAY_CONTAINS([{ category: "shirts", color: "blue" }], { category: "shirts", color: "blue" }), missingFullMatchObject: ARRAY_CONTAINS([{ category: "shirts", color: "blue" }], { category: "shirts" }), containsPartialMatchObject: ARRAY_CONTAINS([{ category: "shirts", color: "blue" }], { category: "shirts" }, true), missingPartialMatchObject: ARRAY_CONTAINS([{ category: "shirts", color: "blue" }], { category: "shorts", color: "blue" }, true) }
StarRocks
docs.starrocks.io › reference › sql functions › array › array_contains
array_contains | StarRocks
Checks whether the array contains a certain element. If yes, it returns 1; otherwise, it returns 0.
RisingWave
risingwave.com › home › blog › mastering sql array contains: a comprehensive guide
Mastering SQL Array Contains: A Comprehensive Guide | RisingWave
June 11, 2024 - In the realm of SQL, sql array contains stands as a pivotal function that enables seamless searching for specific values within arrays. Mastering this function is not just advantageous but essential for SQL enthusiasts aiming to elevate their ...
Medium
paddyalton.medium.com › google-bigquery-how-to-unwrap-an-array-like-field-into-a-wide-format-table-with-one-column-per-14c0686b4fc7
Google BigQuery — how to unwrap an array-like field into a wide-format table with one column per array element | by Paddy Alton | Medium
April 16, 2022 - Imagine a case where someone has ... a field containing arrays of fixed length. Something like this: ... … but for some reason they refuse to change it and tell you it’s your problem. Seems like it should be a simple transformation, right? ... Sadly, this is going to be much more complicated than most people expect. It can be conceptually easier to pass the values into a scripting language (e.g. Python) and work there, but clearly keeping things inside BigQuery is going to ...
Snowflake Documentation
docs.snowflake.com › en › sql-reference › functions › array_contains
ARRAY_CONTAINS | Snowflake Documentation
+------------------------------------------------------------------+ | ARRAY_CONTAINS('HELLO'::VARIANT, ARRAY_CONSTRUCT('HELLO', 'HI')) | |------------------------------------------------------------------| | True | +------------------------------------------------------------------+