Snowflake cross join + lateral flatten

Question

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?

Lukasz Szozda · Accepted Answer · 2022-10-24T20:50:37.270

1

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:

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:

edited Oct 24 '22 at 20:50

answered Oct 24 '22 at 20:15

Lukasz Szozda

162,964
23
234
275

Its a small difference, but in the above is `t` truly cross joined to `cartesian`? In that case wouldnt `col` be non-null in all rows of the result? – Nolan Conaway Oct 24 '22 at 20:46
@NolanConaway I have adjusted the query to the revised requirement. – Lukasz Szozda Oct 24 '22 at 20:51
Ah that solves the immediate issue, but still unclear why the original left join does not behave like any other table join. My intuition is that this is an implementation detail of lateral views (they work like for loops according to snowflake docs), but i didn't find any caveat in the docs on this matter. – Nolan Conaway Oct 24 '22 at 20:53
1

I dug up the relevant documentation: "Unlike the output of a non-lateral join, the output from a lateral join includes only the rows generated from the inline view. The rows on the left-hand side do not need to be joined to the right hand side because the rows on the left-hand side have already been taken into account by being passed into the inline view." https://docs.snowflake.com/en/sql-reference/constructs/join-lateral.html – Nolan Conaway Oct 24 '22 at 21:03

Snowflake cross join + lateral flatten

1 Answers1