Constructing a Query to find the newest rows within the last two hours with with a unique id

Question

I am trying to select all the newest rows(based on the Time column) for each unique Data->>Id within the last two hours. The Data column is a json column which should be accessed by the arrow operator.

Id        Time                    Data
1      2020-08-13 18:13:14       {'Id': "1"}
2      2020-08-13 18:14:12       {'Id': "2"}
3      2020-08-13 18:14:19       {'Id': "1"}
4      2020-08-13 17:13:21       {'Id': "2"}
5      2020-08-13 18:23:54       {'Id': "1"}
6      2020-08-13 13:13:21       {'Id': "2"}

The result should should be something like this with the current time being 2020-08-13 18:15:00

Id        Time                    Data
1      2020-08-13 18:13:14       {'Id': "1"}
4      2020-08-13 17:13:21       {'Id': "2"}

Answer 1

Use distinct on and json accessor:

select distinct on (data ->> 'id') t.*
from mytable t
order by data ->> 'id', time desc