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:

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:

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: