bigfunctions > array_intersect
array_intersect¶
Call or Deploy array_intersect
?
✅ You can call this array_intersect
bigfunction directly from your Google Cloud Project (no install required).
- This
array_intersect
function is deployed inbigfunctions
GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error). - Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
- You may prefer to deploy the BigFunction in your own project if you want 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). Discover the framework
Public BigFunctions Datasets:
Region | Dataset |
---|---|
eu |
bigfunctions.eu |
us |
bigfunctions.us |
europe-west1 |
bigfunctions.europe_west1 |
asia-east1 |
bigfunctions.asia_east1 |
... | ... |
Description¶
Signature
array_intersect(array1, array2)
Description
Returns the intersection of two arrays.
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] |
+--------+
Need help using array_intersect
?
The community can help! Engage the conversation on Slack
For professional suppport, don't hesitate to chat with us.
Found a bug using 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.
For professional suppport, don't hesitate to chat with us.
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
).
Spread the word¶
BigFunctions is fully open-source. Help make it a success by spreading the word!