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
;