SQL
Flattening
The flatten function is a scalar function that converts a list of lists into a single list by concatenating each sub-list together.
Note that this only flattens one level at a time, not all levels of sub-lists.  
Convert a list of lists into a single list:  
```sql
SELECT
flatten([
[1, 2],
[3, 4]
]);
```  
```text
[1, 2, 3, 4]
```  
If the list has multiple levels of lists, only the first level of sub-lists is concatenated into a single list:  
```sql
SELECT
flatten([
[
[1, 2],
[3, 4],
],
[
[5, 6],
[7, 8],
]
]);
```  
```text
[[1, 2], [3, 4], [5, 6], [7, 8]]
```  
In general, the input to the flatten function should be a list of lists (not a single level list).
However, the behavior of the flatten function has specific behavior when handling empty lists and `NULL` values.  
If the input list is empty, return an empty list:  
```sql
SELECT flatten([]);
```  
```text
[]
```  
If the entire input to flatten is `NULL`, return `NULL`:  
```sql
SELECT flatten(NULL);
```  
```text
NULL
```  
If a list whose only entry is `NULL` is flattened, return an empty list:  
```sql
SELECT flatten([NULL]);
```  
```text
[]
```  
If the sub-list in a list of lists only contains `NULL`, do not modify the sub-list:  
```sql
-- (Note the extra set of parentheses vs. the prior example)
SELECT flatten([[NULL]]);
```  
```text
[NULL]
```  
Even if the only contents of each sub-list is `NULL`, still concatenate them together. Note that no de-duplication occurs when flattening. See `list_distinct` function for de-duplication:  
```sql
SELECT flatten([[NULL],[NULL]]);
```  
```text
[NULL, NULL]
```