Home > Enterprise >  Snowflake cross join lateral flatten
Snowflake cross join lateral flatten

Time:10-25

I have a semistructured column that I would like to left lateral join after a cross join.

with t as (
    select parse_json('{"1": 1, "2": 2}') as col
)

, cartesian as (
    select 1 as a union
    select 2 as a union
    select 3 as a
)

select *
from t
cross join cartesian
left join lateral flatten(input => t.col) as js
  on js.key::int = cartesian.a::int

In the above, I would expect the original cross join to expand the result count from 1 to 3; and the left lateral join should not reduce the number of rows returned.

The result I obtain is unexpected:

COL A SEQ KEY PATH INDEX VALUE THIS
{
"1": 1,
"2": 2
}
1 1 1 ['1'] NULL 1 {
"1": 1,
"2": 2
}
{
"1": 1,
"2": 2
}
2 2 2 ['2'] NULL 2 {
"1": 1,
"2": 2
}

Am I crazy, or is the left keyword not doing what it should here?

CodePudding user response:

If I understood question correctly you are aiming for:

with t as (
    select parse_json('{"1": 1, "2": 2}') as col
)
, cartesian as (
    select 1 as a union
    select 2 as a union
    select 3 as a
)
select *
from cartesian 
LEFT JOIN (SELECT * FROM t, TABLE(flatten(input => t.col))) as js
  ON js.key::int = cartesian.a::int;

Output:

enter image description here

Here the flattening of the JSON is perfomed inside inlined view and the result is joined using LEFT JOIN to tally table.


Its a small difference, but in the above is t truly cross joined to cartesian?my intention was to have col populated in all rows above; by cross joining t to `cartesian

It could be adjusted:

with t as (
    select parse_json('{"1": 1, "2": 2}') as col UNION ALL
     select parse_json('{"3": 3}') as col
)

, cartesian as (
    select 1 as a union
    select 2 as a union
    select 3 as a
)

select *
from cartesian 
CROSS JOIN t
LEFT JOIN (SELECT * FROM t, TABLE(flatten(input => t.col))) as js
  ON js.key::int = cartesian.a::int
 AND t.col = js.this
ORDER BY t.col, A;

Output:

enter image description here

  • Related