Flatten without removing rows with null entries

1

I just discovered that flatten removes entries with null value in the flattened field. How do you get around this?

I have a dataset in MongoDB where one of the fields is an array. It is perfectly valid for this array to be null. When I flatten the field in Dremio, all records with no value for the array disappear.

I found a previous post in which the answer was that this was the designed behavior. There has to be a way around this.

1 Like
2

I guess I could do a select flatten(field) as field from table where field is not null UNION all select field from table where field is null … But I hope there’s a better way :slight_smile:

3

@dotjdk Do you have a profile, are there 2 FLATTEN’s in the SQL?

4

Just a single flatten… To test, create a mongo db and insert the following:

db.flatten_arrays.insert([
  {desc: "single_entry_array", array_field: [1234]}, 
  {desc: "multi_entry_array", array_field: [2345,3456]}, 
  {desc: "empty_array", array_field: []}, 
  {desc: "no_array_field"}
])

Issue the following query in dremio:

SELECT desc, flatten(array_field) as array_field FROM flatten_arrays

I would expect five rows, but get only three (two rows with desc = “multi_entry_array” and one row with desc = “single_entry_array”)

Profile attached. Input records: 4 - Output records: 3 (should be 5)

9a8192e7-706b-418e-a6e9-920227d51689.zip (12.0 KB)

5

@dotjdk I just checked and this seems to be an open bug still, let me follow up internally

6

has this issue been resolved yet?

7

Has there been any update on this issue?