Parsing ARRAYS in Big Query
While writing long SQL queries I always wished I could parse a list of items instead of repeating the list in my WHERE clause again and again. Well, this should help removing that redundancy.
Imagine a scenario where you are writing a query with CTEs and the same WHERE condition filters needs to be applied across all of them, you would manually add them in one set and repeat it across the rest but later down the line you are asked to add or remove some of the elements from the conditions.
You would need to make the changes everywhere, which can be frustrating. To make the task simple we can employ the use of a variable CTE with an array and unnest it in the WHERE conditions.
Lets explore this with an example.
The variable_cte is where you can declare your variables that need to be filtered in this case you are filtering for A,B,C,D and storing it as an array in the CTE.
In the query below you are using CROSS JOIN UNNEST to essentially break the array into a list which can be used in the WHERE conditions. The output would look like this.
If you don’t use CROSS JOIN UNNEST you would end up with an array as an output which would look like this.
And passing this in a query would return this error.
In the next article, I will go over how you can generate a date array, and other applications of CROSS JOIN UNNEST.
