Postgres find unique values in json
You can use distinct on()
to do this:
select distinct on (firstname, lastname) id, sender, last_login_datefrom ( select id, sender, last_login_date, sender ->> 'firstName' as firstname, sender ->> 'lastName' as lastname from the_table) torder by firstname, lastname, last_login_date desc
you can do it using window function :
select * from ( select * ,rank() over (partition by sender->> 'firstName',sender->> 'lastName' order by last_login_date desc) rn from yourtable ) twhere rn = 1order by last_login_date desc
db<>fiddle here