Snowflake - Lateral cannot be on the left side of join

Question

I have a variant data type that I am performing a lateral flatten on but I then need to left join one of the json elements to lookup the value for the corresponding ID from another relational table within Snowflake. When I do this it gives me the error "Lateral View cannot be on the left side of join" which doesn't make sense because if I don't include the outer join an create the view and then create an additional view on top of this view, it allows me to perform a left join.

Example:

create or replace view  my_view
copy grants
as
select 
    rowid as row_id,
    siteData.value:siteID::int as site_id,
    es.site_name AS site_name
from 
    "RAW_DATA" raw, 
    lateral flatten(datamap:data, outer => true) siteData
    LEFT join ext_site es on es.siteid = siteData.value:siteID
;

Answer 1

I cannot explain the limitation on not being able to LEFT JOIN after a LATERAL, but here are two potential workarounds to the issue...

Option 1 - Use a CTE (Common Table Expression)

create or replace view my_view
copy grants
as
with my_cte as (
  select 
      rowid as row_id,
      siteData.value:siteID::int as site_id
  from 
      "RAW_DATA" raw, 
      lateral flatten(datamap:data, outer => true) siteData
)
select 
    c.row_id,
    c.site_id,
    es.site_name
from 
    my_cte c
    LEFT join ext_site es on es.siteid = c.site_id
;

Option 2 - Use an inline (anonymous) view

create or replace view my_view
copy grants
as
select 
    c.row_id,
    c.site_id,
    es.site_name
from 
    (
  select 
      rowid as row_id,
      siteData.value:siteID::int as site_id
  from 
      "RAW_DATA" raw, 
      lateral flatten(datamap:data, outer => true) siteData
    ) c
    LEFT join ext_site es on es.siteid = c.site_id
;

Personally, I would use the CTE approach, as I find it much more readable.

Regarding your use of outer => true, that is required only if the VARIANT structure is somehow inconsistent and does not guarantee the existence of a data key within your datamap dictionary having a value (array or dictionary) that itself contains one or more elements or attributes. If it is not required, then I would remove it.

Answer 2

Try this workaround:

create or replace view  my_view
copy grants
as
select 
    rowid as row_id,
    siteData.value:siteID::int as site_id,
    es.site_name AS site_name
from 
    "RAW_DATA" raw
    LEFT join table(flatten(input => raw.datamap:data, outer => TRUE)) siteData
    LEFT join ext_site es on es.siteid = siteData.value:siteID::int
;