Postgres find unique values in json

Question

I am using Postgresql and have a table, with id, sender::jsonb and date, as follows:

id |                                      sender                                        | last login date                 
----+-----------------------------------------------------------------------------------+----------------------------------
  1 | {"firstName": "Ivan",    "lastName": "Ivanov",       "middleName": "Ivanovich", } | 2021-04-10 14:49:36.234504 +00:00   
  2 | {"firstName": "Ivan",    "lastName": "Ivanov",       "middleName": "Ivanovich", } | 2021-04-09 14:49:36.234504 +00:00 
  3 | {"firstName": "Ivan",    "lastName": "Ivanov",       "middleName": "Ivanovich", } | 2021-04-11 14:49:36.234504 +00:00 
  4 | {"firstName": "Nickolai","lastName": "Nickov",       "middleName": "Nikovich", }  | 2021-04-30 14:49:36.234504 +00:00 
  5 | {"firstName": "Nickolai","lastName": "Nickov",       "middleName": "Nikovich", }  | 2021-04-29 14:49:36.234504 +00:00 
  6 | {"firstName": "Vladimir","lastName": "Vladimirovich","middleName": "Putout", }    | 2021-04-15 14:49:36.234504 +00:00 
  7 | {"firstName": "Petr",    "lastName": "Petrov",       "middleName": "Petrovich", } | 2021-04-10 14:49:36.234504 +00:00 
  8 | {"firstName": "Ivan",    "lastName": "Ivanov",       "middleName": "Ivanovich", } | 2021-04-01 14:49:36.234504 +00:00 
  9 | {"firstName": "Ignat",   "lastName": "Ignatov",      "middleName": "Ignatovich", }| 2021-04-06 14:49:36.234504 +00:00 
  10| {"firstName": "Vladimir","lastName": "Vladimirovich","middleName": "Putout", }    | 2021-04-17 14:49:36.234504 +00:00 
  11| {"firstName": "Ivan",    "lastName": "Ivanov",       "middleName": "Ivanovich", } | 2021-04-12 14:49:36.234504 +00:00

p.s.There may be other information in the "sender" column, but the search for uniqueness is only necessary by "firstName", "lastName", "midddleName"

It is necessary to return a result consisting of unique names and with the latest date. In particular, I want to get the result:

id |                                      sender                                        | last login date                 
----+-----------------------------------------------------------------------------------+----------------------------------
  4 | {"firstName": "Nickolai","lastName": "Nickov",       "middleName": "Nikovich", }  | 2021-04-30 14:49:36.234504 +00:00 
  10| {"firstName": "Vladimir","lastName": "Vladimirovich","middleName": "Putout", }    | 2021-04-17 14:49:36.234504 +00:00 
  11| {"firstName": "Ivan",    "lastName": "Ivanov",       "middleName": "Ivanovich", } | 2021-04-12 14:49:36.234504 +00:00 
  7 | {"firstName": "Petr",    "lastName": "Petrov",       "middleName": "Petrovich", } | 2021-04-10 14:49:36.234504 +00:00 
  9 | {"firstName": "Ignat",   "lastName": "Ignatov",      "middleName": "Ignatovich", }| 2021-04-06 14:49:36.234504 +00:00

Everything is very complicated by the fact that json is used. I had thoughts to do - "name" concatenation and perform group by and sorting, but unfortunately it does not work.

"Everything is very complicated by the fact that json is used." -- Yes, indeed. And that is why you should not use JSON but preferably simple columns or another table, at least for that name data as their schema seems to be fixed anyway. — sticky bit
– sticky bit, Commented Apr 17, 2021 at 19:06
Edit the question and show what you have tried already. Explain why/where it failed. Be specific (error message, unexpected result, etc.). — sticky bit
– sticky bit, Commented Apr 17, 2021 at 19:06
@stickybit you're right. but unfortunately in the database this data type — Ilya Y
– Ilya Y, Commented Apr 17, 2021 at 19:09

user330315 · Accepted Answer · 2021-04-17 19:11:31Z

2

You can use distinct on() to do this:

select distinct on (firstname, lastname) id, sender, last_login_date
from (
   select id, sender, last_login_date, 
          sender ->> 'firstName' as firstname,
          sender ->> 'lastName' as lastname
   from the_table
) t
order by firstname, lastname, last_login_date desc

answered Apr 17, 2021 at 19:11

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Ilya Y Over a year ago

thanks! but i have question. why, following the example of this request, I have an error - syntax error at or near "from"?

user330315 Over a year ago

@IlyaY: works for me dbfiddle.uk/…

eshirvana · Accepted Answer · 2021-04-17 19:53:47Z

1

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
 ) t
where rn = 1
order by last_login_date desc

db<>fiddle here

edited Apr 17, 2021 at 19:53

answered Apr 17, 2021 at 19:15

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

2 Comments

Ilya Y Over a year ago

thank you friend. but I am getting the wrong result according to your example.

eshirvana Over a year ago

@IlyaY unless you gave us wrong desired output , this is producing what you showed us.see db<>fiddle in the anaswer

Collectives™ on Stack Overflow

Postgres find unique values in json

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related