How to join with unnest function in athena?

user_12903001 0
Viewed 95

i have this query on Athena

trip.id as tripid
, segment.id as segmentid
, segment.distance as mileage
, segment.maxspeed as maxspeed
, segment.duration as duration
, segment.roadtype as roadtype
, segment.timeslotdata as timeslots
, extract( week from (from_unixtime(trip.referencedate /1000))) as weekyear
, extract( year from (from_unixtime(trip.referencedate /1000))) as year
, extract( month from (from_unixtime(trip.referencedate/1000))) as month
, unn.firstpositionlat
, unn.firstpositionlong
from
trip
, UNNEST(segments) as t(segment)
left join
(
select
position.latitude as firstpositionlat
, position.longitude as firstpositionlong
, position.id as id
from
trip
, UNNEST(segments) as t(segment)
, UNNEST(segment.positions) as t(position)
where
anno = 2021
and mese = 01
and giorno = 01
and tscid = 'XXX'
)
unn
on
segment.startpositionid = unn.id
where
anno = 2021
and mese = 01
and giorno = 01
and tscid = 'XXX'

the problem is that i can't join because of an error on line 16:19, and i have this error

SYNTAX_ERROR: line 16:19: Column 'segments' cannot be resolved

i can't figure out where the problem is. Without the ON statement, this works well.

thank you in advance

unnest presto amazon-athena amazon-s3 sql
1 Answers

Presto (underlying SQL engine for Athena) supports UNNEST only on the right side of CROSS JOIN. For example:

-- sample data
WITH dataset (id, animals) AS (
    values (1, ARRAY['dog', 'cat', 'bird']),
    (2, ARRAY['cow', 'pig'])
) 

-- query
SELECT id, animals, a
FROM dataset
CROSS JOIN UNNEST(animals) AS t (a);

Output:

id animals a
1 [dog, cat, bird] dog
1 [dog, cat, bird] cat
1 [dog, cat, bird] bird
2 [cow, pig] cow
2 [cow, pig] pig

It seems that shorthand notation (from trip, UNNEST(segments) as t(segment)) is not working correctly when followed by another join with on condition, try to just expand it to the full form:

from trip
cross join UNNEST(segments) as t(segment)
left join (...)
on ...
user_25012790
Related