Left join later flatten works not like left join, but the same as inner join

Question

With this data:

create or replace table test_json as
select
parse_json('{
            "include": [
                "x",
                "y",
                "z"
            ]
        }') AS val
union all
select
parse_json('{
  "exclude": [
    "a",
    "b"
  ],
  "include": [
    "d",
    "e"
  ]
}');

I have this query:

with source_cte as (
select val, i.value::TEXT as include_symbol
from test_json, lateral flatten(input=>val:include) as i
)
select
include_symbol,
e.value::TEXT as exclude_symbol
from source_cte LEFT JOIN LATERAL FLATTEN(input=>val:exclude) e

Here is the result:

INCLUDE_SYMBOL    EXCLUDE_SYMBOL
     d                  a
     d                  b
     e                  a
     e                  b

It doesn't return rows with include = x, y, z, like it would if I used INNER JOIN LATERAL FLATTEN, but I'm using LEFT JOIN.

Is it some bug in Snowflake? How to achieve then LEFT JOIN behaviour for such case? I mean to show include_symbol regardless of whether match is found in left join lateral flatten expression (and show nulls in exlude_symbol column in such case).

Answer 1

this problem can be written much simpler:

with test_json(val) as (
    select parse_json($1) from values
        ('{"include": ["x","y","z"]}'),
        ('{"exclude":["a","b"],"include":["d","e"]}')
)
select 
    val
    ,i.value::TEXT as include_symbol
    ,e.value::TEXT as exclude_symbol
from test_json, 
    table(flatten(input=>val:include)) as i,
    table(flatten(input=>val:exclude)) as e;

and then the fix as Mike points at can be used on the exclude flatten:

with test_json(val) as (
    select parse_json($1) from values
        ('{"include": ["x","y","z"]}'),
        ('{"exclude":["a","b"],"include":["d","e"]}')
)
select 
    val
    ,i.value::TEXT as include_symbol
    ,e.value::TEXT as exclude_symbol
from test_json, 
    table(flatten(input=>val:include)) as i,
    table(flatten(input=>val:exclude, outer=>true)) as e;

and then the val can also be dropped:

with test_json(val) as (
    select parse_json($1) from values
        ('{"include": ["x","y","z"]}'),
        ('{"exclude":["a","b"],"include":["d","e"]}')
)
select 
    i.value::TEXT as include_symbol
    ,e.value::TEXT as exclude_symbol
from test_json, 
    table(flatten(input=>val:include)) as i,
    table(flatten(input=>val:exclude, outer=>true)) as e;

which for your table would read like:

select 
    i.value::TEXT as include_symbol
    ,e.value::TEXT as exclude_symbol
from test_json, 
    table(flatten(input=>val:include)) as i,
    table(flatten(input=>val:exclude, outer=>true)) as e;