FLATTEN() removes row completely if flattened field is null


#1

Hello!

When I use flatten() function, I discovered that if the field that has to be flatten is missing in particular row - that row is completely gone. I wonder is there a way to keep such rows in the result?

Example:

JSON:
[
{
“a”:[“foo”,“bar”],
“b”:“smth”
},
{
“b”:“smth_else”
}
]

SELECT flatten(a) as a , b FROM example
returns two rows:

| a | b |
| foo | smth |
| bar | smth |

and I need to keep the “b : smth_else” row as well.

Thanks!


#2

This behavior is as designed. Think of it like an inner join on the key (a) and values. Records without (a) are excluded from the results.