EXPLODE OUTER

[dcmoura]

Currently explode has the following behaviour:

SELECT row.name, row.departments
FROM [
    {"name": "Alice", "departments": [1,4]},
    {"name": "Bob", "departments": [2]},
    {"name": "Charles", "departments": []}
]
EXPLODE row.departments
TO json

Results in:

{"name": "Alice", "departments": 1}
{"name": "Alice", "departments": 4}
{"name": "Bob", "departments": 2}

Omitting the Charles row because of the empty array.

TO DO

• Implement the OUTER modifier that would include cases where departments is empty or NULL.

Result would be:

{"name": "Alice", "departments": 1}
{"name": "Alice", "departments": 4}
{"name": "Bob", "departments": 2}
{"name": "Charles", "departments": null}

Also:

• Make sure EXPLODE without the OUTER modifier works with NULLs and create test