I have this query:
WITH inclusion_exclusion_source AS (
SELECT
parse_json('{
"include": [
"x1",
"x2",
"x3"
]
}') AS val
),
inclusion_exclusion AS (
SELECT
i.val,
ek.value::TEXT AS exclude
FROM
inclusion_exclusion_source i
LEFT JOIN LATERAL FLATTEN(INPUT => i.val:exclude) ek
)
SELECT *
FROM inclusion_exclusion;
It returns 0 rows, as I would use INNER JOIN LATERAL FLATTEN, instead of LEFT JOIN LATERAL FLATTEN.
Is it some bug in Snowflake?
How to achieve then LEFT JOIN behaviour for such case? I mean to show i.val regardless of whether match is found in flatten expression.