array_intersect¶
array_intersect(array1, array2)
Description¶
Returns the intersection of two arrays.
Usage¶
Call or Deploy array_intersect ?
Call array_intersect directly
The easiest way to use bigfunctions
array_intersectfunction is deployed in 39 public datasets for all of the 39 BigQuery regions.- It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
- (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)
Public BigFunctions Datasets
| Region | Dataset |
|---|---|
eu |
bigfunctions.eu |
us |
bigfunctions.us |
europe-west1 |
bigfunctions.europe_west1 |
asia-east1 |
bigfunctions.asia_east1 |
| ... | ... |
Deploy array_intersect in your project
Why deploy?
- You may prefer to deploy
array_intersectin your own project to build and manage your own catalog of functions. - This is particularly useful if you want to create private functions (for example calling your internal APIs).
- Get started by reading the framework page
Deployment
array_intersect function can be deployed with:
pip install bigfunctions
bigfun get array_intersect
bigfun deploy array_intersect
Examples¶
select bigfunctions.eu.array_intersect([1, 2, 3], [2, 6, 7])
select bigfunctions.us.array_intersect([1, 2, 3], [2, 6, 7])
select bigfunctions.europe_west1.array_intersect([1, 2, 3], [2, 6, 7])
+--------+
| result |
+--------+
| [2] |
+--------+
Use cases¶
Use Case: Finding Common Interests
Imagine you have a dataset of users and their interests, stored as arrays. You want to find users who share at least one common interest with a specific user.
WITH UserInterests AS (
SELECT 'user1' AS user_id, ['reading', 'hiking', 'coding'] AS interests UNION ALL
SELECT 'user2' AS user_id, ['coding', 'gaming', 'music'] AS interests UNION ALL
SELECT 'user3' AS user_id, ['cooking', 'hiking', 'photography'] AS interests UNION ALL
SELECT 'user4' AS user_id, ['gaming', 'sports', 'travel'] AS interests
),
TargetUserInterests AS (
SELECT interests FROM UserInterests WHERE user_id = 'user1' -- Let's say user1 is our target user
)
SELECT ui.user_id
FROM UserInterests AS ui, TargetUserInterests AS tui
WHERE bigfunctions.YOUR_REGION.array_intersect(ui.interests, tui.interests) IS NOT NULL -- Replace YOUR_REGION with your BigQuery region
AND ui.user_id != 'user1'; -- Exclude the target user himself
This query uses array_intersect to find the intersection of interests between each user and the target user ('user1'). If the intersection is not null (meaning they have at least one common interest), the user_id is returned. The final AND clause ensures the target user isn't included in the results.
Other Use Cases:
- Product Recommendations: Find products with features in common with a user's previously purchased items.
- Skill Matching: Identify candidates who possess a required set of skills for a job opening.
- Event Filtering: Show events that match a user's selected categories.
- Data Deduplication: Detect records with overlapping data points, like lists of keywords or tags.
- Inventory Management: Find items common to multiple warehouses.
The key is that whenever you need to determine shared elements between two arrays, array_intersect becomes a valuable tool. Remember to replace YOUR_REGION with the appropriate BigQuery region for your project (e.g., us, eu, us-central1).
Need help or Found a bug?
Get help using array_intersect
The community can help! Engage the conversation on Slack
We also provide professional suppport.
Report a bug about array_intersect
If the function does not work as expected, please
- report a bug so that it can be improved.
- or open the discussion with the community on Slack.
We also provide professional suppport.
by adding a 
on