Skip to content

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_intersect function 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_intersect in 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.


Show your ❤ by adding a ⭐ on