Reputation: 1055
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