SimonB
SimonB

Reputation: 1055

Redshift join on "SUPER" type array

I have a SUPER column that contains an object with array.

{"list":["MY","ID","SG","TW","KR","CN"]}

Would like to join tables on that array.

Had hoped that this would be simple and work in the same way one would join to pg_group

myfield = any(grolist)

However I am finding that a JSON array isn't the same kind of array !

select f."country", lu."group" 
from    test.fact f inner join test.country_lookup lu  
on f."country" = any(lu."country".list)

ERROR: op ANY/ALL (array) requires array on right side

Instead I have to unnest the whole thing ..

select f."country", l."group"
from 
  test.fact f
  left outer join 
  (
    Select lu."group", i::text as country
    from  test.country_lookup lu,
    lu."country".list i
  ) l
  on f."country" = l.country

I mean this last version 'works', but it is rather cumbersome.

Anyone know of a neater way ?

Upvotes: 1

Views: 458

Answers (0)

Related Questions