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.