Redshift super type : left outer unest

Question

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"           
     }      
    ]   
  } 
 ]

Answer 1

U have to union all between the table and the unnest.

Select col, el
From tbl t, arraycol el

Union all

Select col, null
From tbl
Where isnull(get_array_length(arraycol),0)=0

There’s no other way to do it, you can’t left join an unnest, and you can’t put logic into an unnest itself.

Well at least the above is the way ud do it if u wanted an incrementally refreshed mat view. If ur able to use a subquery then:

Select col, el from (
Select col, case when isnull(get_array_length(arraycol),0)=0 
    then json_parse(‘[null]’) 
    else arraycol end newarraycol
From tbl) t, t.arraycol el