Understanding BigQuery Array Functions

Understanding array functions in BigQuery is essential for anyone working with data. Array functions in BigQuery help in manipulating lists of data, such as combining multiple values into a single list or changing elements within these lists.

This article will guide you through essential array functions and how to avoid common mistakes. It's designed for data professionals who want to improve their skills in managing and analyzing data. You'll learn how to create, manipulate, and analyze arrays effectively, making your data tasks simpler and more efficient.

Starting with BigQuery array functions opens up possibilities for handling data more effectively. Arrays let you organize data into neat, ordered lists where each piece of data is similar. This makes your data analysis smoother, whether grouping data for a clearer view or doing lots of operations simultaneously.

Understanding how to use arrays can simplify and streamline your data tasks, helping you gain the insights you need faster.

When working with array functions in BigQuery, it's crucial to be aware of certain limitations. These constraints can impact how you structure your queries and analyze your data, so understanding them upfront will help you navigate BigQuery array functions more effectively and avoid common pitfalls in your data projects.

Some of the constraints are:

• Handling Nested Arrays: Navigating through layers of arrays can be complex and requires careful structuring of queries. For example, if you have an array of arrays, accessing elements requires multiple steps.
• Uniform Data Types: All elements within an array must be of the same data type to ensure consistency and prevent errors. For instance, an array containing both strings and integers will cause type conflicts.
• Processing Limits: Be mindful of BigQuery's processing capabilities, especially when working with large arrays or complex operations. For instance, processing a billion-element array can strain resources.
• Query Performance: Large or poorly structured array queries can slow down performance, so optimization is critical. For example, a poorly optimized array query can take several minutes to execute.
• Data Import/Export Limitations: There may be constraints on the size and structure of arrays when importing or exporting data. Exporting an array with more than 1 million elements may require special handling.
• Compatibility with Other Functions: Not all SQL functions can be directly applied to arrays, which may require additional steps to manipulate the data. Aggregation functions like SUM may require additional steps to work with arrays. 2-3 extra lines of code per incompatible function may be required additionally.

BigQuery's Array functions are powerful tools for data manipulation, enabling tasks like creating arrays with the ARRAY function, merging them, or transforming them into strings.

These functions facilitate complex analyses, such as generating series or handling time-series data. Understanding and applying these functions effectively can significantly enhance BigQuery's data processing and analysis capabilities.

This function is useful when you need to group multiple values into a single entity that can be easily manipulated or analyzed. Arrays created with this function can store elements of any data type, but all elements must be of the same type.

Using ARRAY enhances data organization, facilitates complex calculations, and enables efficient data manipulation within queries.

ARRAY Syntax:

Here:

• ARRAY[]: The function and square brackets indicate the start of the array creation.
• expression: This is the value or set of values you want to include in the array.
• AS element: This part is optional and defines each item in the array when using a subquery or a more complex expression.

Suppose you're analyzing survey data in which respondents ranked their top three product features.

• SELECT: This keyword is used to specify that you're querying data.
• ARRAY[1, 2, 3]: This creates an array containing the elements 1, 2, and 3.
• AS example_array: This names the created array example_array, which you can reference in your query for further analysis.

This query creates an array of those top three features, 1, 2, and 3, for each respondent, making it easier to analyze the data collectively rather than as separate values.

ARRAY_CONCAT is a BigQuery function that merges two or more arrays into a single array. This function is proper when you need to combine data from multiple sources or consolidate similar data types for analysis.

The primary benefit is its ability to simplify data manipulation by aggregating arrays, making data more accessible for further processing or analysis. This function ensures data uniformity and efficiency in handling array-based datasets.

ARRAY_CONCAT Syntax:

Here:

• array1, array2, ...: These are the arrays you want to concatenate. You can combine two or more arrays as long as they contain the same data type.

Imagine you're analyzing survey data from two different periods, and you have the responses as scores in two separate arrays. To analyze the combined data, you can merge these arrays to create a single dataset for analysis.

• [1,2]: Represents scores from the first survey period.
• [3,4]: Represents scores from the second.
• [5,6]: Represents scores from the third.

Using ARRAY_CONCAT, these arrays are merged into [1,2,3,4,5,6], named combined_scores, making it easier to perform comprehensive data analysis across three periods.

The ARRAY_LENGTH function is a straightforward and efficient way to find out how many elements are in an array in BigQuery. This function is handy for working with arrays with different numbers of elements and helps your queries adjust automatically to the data they work with.

ARRAY_LENGTH Syntax:

Here:

• array: The array for which you want to determine the length. It must be an array data type, meaning it is a collection of elements that are of the same type.
• ARRAY_LENGTH: ​​The function computes and returns the total count of these elements, providing you with the size of the array.

Suppose you're managing a database of customer orders, where each order is an array of item IDs. You could use ARRAY_LENGTH to find out how many items are in each order.

• [1, 2, 3]: Represents an order of three items, with item IDs 1, 2, and 3.
• AS array_length: Names the function's output for easy reference, showing that this order contains three items.

The example's output would be a single integer value representing the number of elements in the array. In this case, the array [1, 2, 3] has three components. This indicates that the array length, or the number of items in the given array, is 3.

The ARRAY_REVERSE function in BigQuery is a simple yet powerful tool for reversing the order of elements in an array. It can be useful when you need to invert data sequences for analysis, such as reversing time series data or the order of operations in a process. The ability to reverse arrays can also aid in data preparation, making it easier to compare or align datasets.

ARRAY_REVERSE Syntax:

Here:

• array: The array you want to reverse. The elements in this array must be of the same data type.

Imagine you have a dataset of monthly sales figures for the past year in descending order (from the most recent month to the oldest). To analyze the sales trend from the beginning of the year, you can use ARRAY_REVERSE to flip the order of the sales data, making it easier to perform your analysis.

• [1, 2, 3]: This query starts with an array containing the elements 1, 2, and 3.
• ARRAY_REVERSE: It applies the ARRAY_REVERSE function directly to the array to reverse its order.
• AS reversed_array: The output column is renamed to reversed_array for clarity.

The result of this query is an array [3, 2, 1], which is the reverse of the original array.

The ARRAY_TO_STRING function in BigQuery is a handy tool for converting arrays into string representations. This function takes an array and a delimiter as inputs and returns a single string where each element of the array is separated by the specified delimiter.

ARRAY_TO_STRING Syntax:

Here:

• array: The array you want to convert into a string.
• delimiter: The string that separates each element in the output string.

Suppose, we're converting an array of integers [1, 2, 3] into a single string. The elements of the array need to be separated by a comma (,). The result of this query will be a string 1,2,3, which is stored in the alias string_array.

• Array of words [1, 2, 3]: This starts with an array containing the words apple, orange, and peach.
• Alias/storage name: The array is given the alias or storage name string_array.

This function is incredibly useful in scenarios where you need to export array data for use in applications that require string input or when presenting data in a format that's easier for end-users to read.

💡 If handling text data manually is causing you trouble, find a tool that can simplify string manipulation and eliminate the constraints of manual methods. Discover our comprehensive guide on using string functions in BigQuery for efficient text data processing and transformation.

The GENERATE_ARRAY function in BigQuery creates an array consisting of a sequence of numbers, starting from a specified beginning value, ending at a specified stop value, and incrementing by a defined step.

It's useful for generating series of data for analysis, creating indexes, or even for use in iterative operations within queries.

GENERATE_ARRAY Syntax:

Here:

• start: The starting value of the sequence. It defines where the array begins.
• end: The ending value of the sequence. The array will include values up to this point, but not exceeding it.
• step: The increment between each value in the sequence. It determines how the values in the array progress from start to end.

The following example creates an array starting at 1, ending at 15, and increments by 2. The sequence_array will contain the values [1, 3, 5, 7, 9, 11, 13, 15].

• 1: The start value, indicates where the array starts.
• 15: The end value, of the array, will include values up to this number.
• 2: The step value, determining the interval between each number in the array.

This function is ideal for scenarios requiring a quick generation of number sequences, such as setting up test data, performing repetitive operations within a controlled loop, or even partitioning data based on numeric intervals.

The GENERATE_DATE_ARRAY function in BigQuery creates an array of dates between a specified start date and end date, incrementing by a given step interval. This is particularly useful for generating time series data for analysis, filling gaps in datasets, and ensuring continuity in reports.

GENERATE_DATE_ARRAY Syntax:

Here:

• start_date: The beginning date of the array (inclusive).
• end_date: The final date of the array (inclusive).
• INTERVAL step_amount DAY: The step interval between dates in the array, specified in days.

Suppose we are creating an array of dates from January 1, 2023, to January 7, 2023, with a step interval of 1 day. The following syntax generates a continuous series of dates.

• start_date: '2023-01-01' marks the starting point of the date array.
• end_date: '2023-01-07' defines the end point, making the array span a week.
• INTERVAL 1 DAY: This specifies that the array should contain every day between the start and end dates, without skipping any.

This function is convenient when you need to analyze daily metrics over a specific period, ensuring no date is missed. Whether you're tracking daily sales, website traffic, or any other daily metric, GENERATE_DATE_ARRAY helps maintain a structured, chronological analysis framework.

GENERATE_TIMESTAMP_ARRAY creates an array of timestamp values starting from a specified start timestamp to an end timestamp, incremented by a defined step interval.

GENERATE_TIMESTAMP_ARRAY Syntax:

Here:

• start_timestamp: The starting point of the timestamp array.
• end_timestamp: The endpoint for the timestamp array.
• INTERVAL step_amount: The increment step between each timestamp in the array.

The following is an example of creating an array of timestamps starting from January 1, 2023, at 00:00 hours to January 2, 2023, at 00:00 hours, with a 12-hour interval between each timestamp.

• '2023-01-01 00:00:00 UTC': The start timestamp, beginning of January 1, 2023.
• '2023-01-02 00:00:00 UTC': The end timestamp, end of January 1, 2023, essentially the start of January 2.
• INTERVAL 12 HOUR: The step interval, indicating that each timestamp in the array will be 12 hours apart.

This function can be highly beneficial for analyzing events or metrics that occur over specific time intervals, such as daily sales, website traffic peaks, or monitoring system performance metrics over time.

The UNNEST function in BigQuery is essential for transforming array elements into individual rows, making it easier to work with each item directly in your SQL queries.

This function is particularly useful when you need to join data in an array with other tables or when you're looking to analyze or manipulate individual array elements separately.

UNNEST Syntax:

Here:

• array: The array to be unnested or expanded into separate rows.

Suppose you're working with a simple array containing names. By applying the UNNEST function, you will get output as the names 'Alice', 'Bob', and 'Charlie' as separate rows.

• 'Alice', 'Bob', 'Charlie': This array of names is expanded so that each name becomes its own row in the output.
• SELECT name: This part of the query specifies that you want to select the names from the unnested array, resulting in a table where each row is a name from the original array.

This functionality is incredibly useful in scenarios where you're dealing with user data stored in arrays, such as names, and you need to perform operations or analyses on each individual item, like filtering for specific names or counting occurrences.

If you want to enhance your skills in Google BigQuery, it's important to explore more advanced functions.

• Conversion Functions: Acquire knowledge of conversion functions to convert data types and formats within your BigQuery projects seamlessly.

• Aggregate Functions: Build expertise in aggregate functions to consolidate and examine extensive datasets in BigQuery effectively.

• DML : Improve your proficiency in DML for executing updates, deletions, and insertions that alter the data stored in BigQuery.

• Numbering Functions: Numbering functions assign unique or ranked numbers to rows within a result set, facilitating the ordering and partitioning of data.

• Navigation Functions: Navigation functions allow access to values in other rows without the need for self-joins, making it easier to lead or lag data within partitions.

• Conditional Expressions: These functions enable logic-based operations in BigQuery, returning different results based on specified conditions.

• Statistical Aggregate Functions: These functions provide advanced statistical operations, including the calculation of standard deviations, variances, and other statistical measures on your data.

Common errors when using array functions in BigQuery often stem from type mismatches, handling null or empty arrays, or incorrect function inputs. Awareness and proactive management of these issues can streamline your data analysis process, making it more efficient and error-free.

When using the ARRAY function in BigQuery, an error occurs if the subquery returns more than one column.

 🚫 Error:

This error typically happens when the ARRAY function's subquery generates multiple data columns instead of one. BigQuery expects the ARRAY function to operate on a single column of values, so having multiple columns causes a conflict.

✅ Solution:

To resolve this error, ensure that the subquery produces only a single column of data. If you need to work with multiple columns, combine them into a single structured column using the SELECT AS STRUCT syntax.

Syntax for the single column:

Syntax for multiple columns as STRUCT:

Suppose you have a table named employees with columns first_name and last_name and want to create an array of full names. Using the ARRAY function directly would result in the multi-column array error.

Here’s the difference between incorrect and correct syntax.

Incorrect Syntax:

Correct Syntax:

In this corrected query, we use SELECT AS STRUCT to combine first_name and last_name into a single structure. This ensures the ARRAY function receives a single-column input, resolving the multi-column array error.

When working with null or empty arrays, there can be uncertainty about the behavior of array functions.

🚫 Error:

This error can occur when functions like ARRAY encounter null values or when dealing with arrays that have no elements. Depending on the function and the input data, the behavior can vary, leading to unexpected results if not handled correctly.

✅ Solution:

The ARRAY function returns an empty array if there are no rows in the input, ensuring consistent behavior even with null or empty arrays. However, functions like ARRAY_CONCAT return NULL if any input array is NULL, requiring careful handling to avoid unexpected outcomes.

The syntax for using a subquery to generate an array:

The syntax for concatenating multiple arrays:

Suppose you have a table with a column named ‘revenue’, and you want to create an array of all positive revenue.

Let's see how you can handle null or empty arrays.

Incorrect Query:

Correct Query:

In this corrected query, we use COALESCE to replace null values with 0 before creating the array. This ensures that the ARRAY function doesn't encounter null or empty arrays, thus avoiding uncertainty about the function's behavior.

This error occurs when attempting to concatenate arrays that have different element types using the ARRAY_CONCAT function.

🚫 Error:

The ARRAY_CONCAT function expects all arrays passed to it to have the same element type. If there's a mismatch in the element types of the concatenated arrays, BigQuery will throw this error to indicate the type inconsistency.

✅ Solution:

To resolve the ARRAY_CONCAT Type Mismatch error, ensure all arrays being passed to the ARRAY_CONCAT function have elements of the same data type. To avoid this error, you may need to perform data type conversions or ensure consistent data entry.

The following is the syntax for concatenating multiple arrays into a single array:

Ensure that array1, array2, and any additional arrays provided to ARRAY_CONCAT contain elements of the same data type.

Suppose you have two arrays, one containing numbers and the other containing strings. Concatenating these arrays directly with ARRAY_CONCAT would result in the Type Mismatch error.

Here's how you can correct it:

Incorrect Query:

Correct Query:

In the corrected query, both arrays [1, 2, 3] and [4, 5, 6] have elements of the same data type (numbers), avoiding the Type Mismatch error.

Unexpected results occur when using ARRAY_LENGTH on NULL or empty arrays.

🚫 Error:

This error can occur when you use the ARRAY_LENGTH function on an array that is either NULL (contains no data) or empty. In such cases, ARRAY_LENGTH behaves differently depending on whether the array is NULL or empty, which can lead to unexpected outcomes in your queries.

✅ Solution:

To handle this error, it's essential to understand the behavior of ARRAY_LENGTH:

• If the array is NULL, ARRAY_LENGTH will return NULL.

• If the array is empty (contains no elements), ARRAY_LENGTH will return 0.

The following is the syntax for computing the length of the array expression:

Consider a scenario where you have a table of sources with an array column source_list that may sometimes be NULL or empty. You want to determine the length of each array in the source_list column without encountering errors due to NULL or empty arrays.

Incorrect Query:

In this query, if item_list contains NULL or empty arrays, ARRAY_LENGTH may produce unexpected results or errors.

Correct Query:

Here, we use a CASE statement to handle NULL arrays, returning NULL for NULL arrays and the actual array length for non-NULL arrays. This approach ensures that the ARRAY_LENGTH function behaves predictably and doesn't cause unexpected errors or results.

This error occurs when using the ARRAY_REVERSE function on a non-array type input.

🚫 Error:

ARRAY_REVERSE is specifically designed to reverse the order of elements within an array. When you attempt to use it on a non-array type, such as a single value or a different data type, BigQuery will throw this error to indicate that the input is incompatible with the function.

✅ Solution:

To resolve the Non-Array ARRAY_REVERSE Input error, ensure that the input provided to the ARRAY_REVERSE function is indeed an array. Check the data type and structure of the input to avoid this error.

The following is the syntax for reversing the order of elements within the specified array:

The input array must be an actual array data type containing elements in an ordered sequence.

Suppose you mistakenly try to reverse a single value instead of an array using ARRAY_REVERSE. Here's an example of the error and its correction:

Incorrect Query:

Corrected Query:

In the corrected query, we provide an actual array (['H', 'e', 'l', 'l', 'o']) as input to ARRAY_REVERSE, ensuring that the function operates on an array and avoids the Non-Array ARRAY_REVERSE Input error.

This error occurs when the ARRAY_TO_STRING function produces incorrect string results, often due to incorrect usage of delimiters or null_text for NULL array elements.

🚫 Error:

ARRAY_TO_STRING converts an array into a string, with optional parameters for specifying delimiters and handling NULL array elements. If these parameters are not used correctly, unexpected string outputs may not align with your intended formatting.

✅ Solution:

To address ARRAY_TO_STRING delimiter Issues, double-check the usage of delimiters and ensure they are appropriately placed within the function. Additionally, if dealing with NULL array elements, verify that the null_text parameter is used correctly to handle these cases and avoid unexpected results.

The following syntax converts an array into a string, using the specified delimiter to separate elements. The optional null_text parameter defines the replacement text for NULL array elements:

Suppose you want to convert an array of names into a comma-separated string using ARRAY_TO_STRING. Here's an example that illustrates the correct usage:

Incorrect Query:

Corrected Query:

In the corrected query, we provide the null_text parameter as 'Unknown' to handle NULL array elements, ensuring that the ARRAY_TO_STRING function produces the desired string output without Delimiter Issues.

This error occurs when using a step expression of 0 or NaN (Not a Number) in the GENERATE_ARRAY function.

🚫 Error:

If the step expression for the GENERATE_ARRAY function is set to 0 or NaN, it can lead to mathematical errors or an infinite loop, causing the error.

✅ Solution:

To resolve the GENERATE_ARRAY Step Expression error, ensure that the step expression provided to GENERATE_ARRAY is a non-zero, non-NaN value. Using a valid step expression ensures the function can generate the desired sequence of numbers without encountering errors.

The following syntax creates an array of numbers from start to end in increments defined by step:

Suppose you want to generate an array of numbers from 1 to 10 with a step of 2 using GENERATE_ARRAY.

Here's an example of how to avoid the error:

Incorrect Query:

Corrected Query:

In the corrected query, we use a step expression of 2 instead of 0, ensuring that the GENERATE_ARRAY function can generate the array without encountering step expression errors.

When using GENERATE_DATE_ARRAY, you may encounter unexpected empty arrays or errors if the start and end dates are not correctly ordered or if the step expression is invalid.

🚫 Error:

GENERATE_DATE_ARRAY is used to create an array of dates within a specified range, defined by a start date, an end date, and an optional step expression. If the start date is after the end date or if the step expression is not valid, it can lead to errors or empty arrays.

✅ Solution:

To avoid GENERATE_DATE_ARRAY date order errors, ensure that the start date is before the end date in chronological order. Additionally, verify that the step expression, if used, is valid and aligns with the desired interval between dates.

The following is the syntax for generating an array of dates from start_date to end_date, incrementing by step_expression if provided:

Suppose you want to generate an array of dates from January 1, 2023, to January 7, 2023, with a step of 1 day using GENERATE_DATE_ARRAY.

Here's the common error made in syntax, along with the correct syntax:

Incorrect Query:

Corrected Query:

In the corrected query, we ensure that the start date ('2023-01-01') comes before the end date ('2023-01-07') to avoid Date Order errors in GENERATE_DATE_ARRAY.

This error occurs when using the GENERATE_TIMESTAMP_ARRAY function results in errors or empty arrays due to incorrect ordering of start and end timestamps, or incorrect interval specification.

🚫 Error:

GENERATE_TIMESTAMP_ARRAY is used to create an array of timestamps within a specified range and interval. If the start timestamp is after the end timestamp or if the interval is incorrectly specified, it can lead to errors or produce an empty array.​

✅ Solution:

To avoid Timestamps in GENERATE_TIMESTAMP_ARRAY errors, ensure that the start timestamp is before the end timestamp and that the interval is correctly specified based on your requirements. Double-checking these parameters will help generate the desired array of timestamps without issues.

The following syntax generates an array of timestamps starting from start_timestamp up to (and including) end_timestamp, with the specified interval between timestamps:

Suppose you want to generate an array of timestamps for every hour within a specific date range.

Here's an example illustrating the common error made along with the correct usage:

Incorrect Query:

Corrected Query:

In the corrected query, we ensure that the start timestamp ('2023-01-01 00:00:00 UTC') is before the end timestamp ('2023-01-01 23:59:59 UTC') and specify the correct interval of 1 hour, resulting in a valid array of timestamps without any errors or empty arrays.

With the foundation laid in manipulating and analyzing array data in BigQuery, you can elevate your reporting and analytics work to new heights. The OWOX BI BigQuery Reports Extension further enhances this capability by providing seamless integration with your BI tools, enabling dynamic array manipulation, advanced data analysis, and efficient reporting directly from your BigQuery data.

Leveraging the techniques and functions discussed alongside powerful tools like the OWOX BI BigQuery Reports Add-on empowers you to optimize your queries, uncover deeper insights, and drive more informed decisions across your data projects.