Skip to content

remove_value

remove_value(arr, value)

Description

Return an array with all values except value.

Usage

Call or Deploy remove_value ?
Call remove_value directly

The easiest way to use bigfunctions

  • remove_value 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 remove_value in your project

Why deploy?

  • You may prefer to deploy remove_value 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

remove_value function can be deployed with:

pip install bigfunctions
bigfun get remove_value
bigfun deploy remove_value

Examples

select bigfunctions.eu.remove_value([1, 4, 3, 8], 4)
select bigfunctions.us.remove_value([1, 4, 3, 8], 4)
select bigfunctions.europe_west1.remove_value([1, 4, 3, 8], 4)
+-----------+
| arr       |
+-----------+
| [1, 3, 8] |
+-----------+

Use cases

Imagine you have a table of user preferences where each user has a list of favorite colors stored in an array. You want to remove a specific color from a user's preference list.

Table Schema:

CREATE OR REPLACE TABLE `your_project.your_dataset.user_preferences` (
  user_id INT64,
  favorite_colors ARRAY<STRING>
);

INSERT INTO `your_project.your_dataset.user_preferences` (user_id, favorite_colors) VALUES
(1, ['red', 'blue', 'green', 'yellow']),
(2, ['blue', 'green', 'purple']),
(3, ['red', 'orange', 'yellow']);

Use Case: Removing 'blue' from user preferences:

SELECT
    user_id,
    bigfunctions.your_region.remove_value(favorite_colors, 'blue') AS updated_favorite_colors
FROM
    `your_project.your_dataset.user_preferences`;

Result:

+---------+-------------------------+
| user_id | updated_favorite_colors |
+---------+-------------------------+
|       1 | ['red', 'green', 'yellow'] |
|       2 | ['green', 'purple']       |
|       3 | ['red', 'orange', 'yellow'] |
+---------+-------------------------+

This query uses the remove_value function to remove the color 'blue' from each user's favorite_colors array. Users who didn't have 'blue' in their list remain unaffected. Replace your_region with the appropriate BigQuery region for your project (e.g., us, eu, us-central1).

Other scenarios where remove_value can be useful:

  • Product Recommendations: Removing previously purchased items from a recommendation list.
  • Inventory Management: Removing out-of-stock items from a product catalog.
  • Data Cleaning: Removing specific erroneous values from a dataset.
  • Filtering Search Results: Removing unwanted tags or categories from a search query.
  • Access Control: Removing revoked permissions from a user's access list.

In essence, whenever you need to dynamically filter elements from an array based on their value, the remove_value function provides a concise and efficient solution.


Need help or Found a bug?
Get help using remove_value

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about remove_value

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