Error handle a missing key in SQL mapping

user_7535899 0
Viewed 158

I am new to Presto SQL and I am trying to map items but receiving a 'Key not present in map:formId' error. I was wondering how to handle mapping when one of the keys is missing? When I remove that particular key, the query runs just fine. But in the future, this key may have data and so removing it does not help in the long run. I have seen suggestions to use element_at but I am not sure how to implement that. Additionally, I have tried COALESCE(TRY() but it will only take one argument and not an array.

What I currently have:


with list as(
select t.week,t.event,t.team,t.continent,t.country
from table as t 
where t.date >= '2022-02-10'
and t.team_name IN ('charlie','Anna') 
and COALESCE(
               t.info['formId'], 
               t.info['roomId'],
               t.info['hall_name'],
               t.info['teAM_id'])  IN ('catalog 0', 'catalog 1', 'catalog 2', 'catalog 3', 'catalog 4')
)
select * from list

The error I am receiving is 'Key not present in map:formId'

presto mapping sql
1 Answers

Looking at the Presto documentation for Map Functions and Operators, it looks as if you should be able to replace t.info['formId'] with element_at(t.info, 'formId') to get the value if it exists and NULL if it doesn't, which COALESCE will then ignore and move onto the next item (t.info['roomId']). You might need to do that for all four t.info['key'] items — you know your data better than I do.

Slide 55 of Presto Training - Advanced SQL Features in Presto supports my hypothesis.

user_151680
Related