array_union.md

Use Case: Combining Product Categories

Imagine you have an e-commerce platform, and you store product categories as arrays in a BigQuery table. You want to display all unique categories associated with a product, even if they come from different sources.

Table Schema:

CREATE OR REPLACE TABLE `your_project.your_dataset.products` (
  product_id STRING,
  categories_source1 ARRAY<STRING>,
  categories_source2 ARRAY<STRING>
);

INSERT INTO `your_project.your_dataset.products` (product_id, categories_source1, categories_source2) VALUES
('product1', ['Electronics', 'Smartphones'], ['Mobile Phones', 'Gadgets']),
('product2', ['Clothing', 'Shoes'], ['Footwear', 'Accessories']);

Query with array_union:

SELECT
    product_id,
    bigfunctions.your_region.array_union(categories_source1, categories_source2) AS all_categories
  FROM
    `your_project.your_dataset.products`;

Result:

+-----------+-------------------------------------+
| product_id | all_categories                     |
+-----------+-------------------------------------+
| product1   | ['Electronics', 'Smartphones', 'Mobile Phones', 'Gadgets'] |
| product2   | ['Clothing', 'Shoes', 'Footwear', 'Accessories']        |
+-----------+-------------------------------------+

Explanation:

The array_union function effectively combines the arrays from categories_source1 and categories_source2, eliminating duplicate category names. This gives you a single array (all_categories) containing all unique categories associated with each product. This can be beneficial for filtering, faceting, or displaying comprehensive product information on your website.

Other Use Cases:

• Merging User Interests: Combining user interests from different sources (e.g., browsing history, explicit preferences) into a single unified list.
• Consolidating Tags: Merging tags or keywords assigned to articles or other content from multiple sources.
• Combining Lists of Features: Merging lists of product features from different databases or APIs. Basically anytime you need to create a distinct list from multiple lists, array_union is a good choice.