I am unnesting some JSON in Redshift.
The below code works but …
… if a record does not have a ‘tags’ element then the entire row is ignored as is effectively and ‘inner’ join.

I read that you cannot do ‘left outer’ in this context.
Still, wanted to check if there are any workarounds ?

I recall in Athena/Presto you could coalesce and prove a default array() if the subset was null ?

select
    src.ac, 
    src."day", 
    src.region, 
    i.resourcearn, 
    t.key, 
    t.value
from 
    ap_rs_meta_use_dev.getresources  src
    ,src.resourcetagmappinglist i
    ,i.tags t

Example data :

[
{
“resourcearn”:”arn:aws:ec2:us-xxxx-x:123456789012:volume/vol-##############b”,
“tags”: [
{
“key”: “ManagedBy”,
“value”: “#########”
},{
“key”: “Environment”,
“value”: “Production”
}
]
}
]