How select a row and count row in single query in postgresql?

Question

I have a table in postgresql as follow:

 id | chat_id |  content  | time |                 read_times                 
----+---------+-----------+------+-------------------------------------------------------------------------
  1 | chat_10 | content_1 |  t1  | [{"username": "user1", "time": 123}, {"username": "user2", "time": 111}]
  2 | chat_10 | content_2 |  t2  | [{"username": "user2", "time": 1235}]
  3 | chat_10 | content_3 |  t3  | []
  4 | chat_11 | content_4 |  t4  | [{"username": "user1", "time": 125}, {"username": "user3", "time": 121}]
  5 | chat_11 | content_5 |  t5  | [{"username": "user1", "time": 126}, {"username": "user3", "time": 127}]

Note: t1 < t2 < t3 < t4 < t5

After every user read a message, we registered it in read_times column(user2 read a message with id 2 at time 1235), Now I want to get user chat list with unread count chats. for user1 the result is as follow:

 chat_id |  content  |  unread_count
 --------+-----------+--------------
 chat_10 | content_3 |       2
 chat_11 | content_5 |       0

Note: unread_count is messages count that user didn't read in a caht_id.

Is it possible with one query?

Seems like "unread count" for `user1` considers any row that doesn't include `"username": "user1"`. — The Impaler, Jul 26 '21 at 14:27
still its not clear , why for `user1` , in chatid 10 and content 3, unread_count is 2 ?? — eshirvana, Jul 26 '21 at 14:59

score 1 · Accepted Answer · answered Jul 26 '21 at 15:42

1

First, you must extract the user names for each chat_id and content with json_array_elements() function and with FIRST_VALUE() window function get the last content of each chat_id.
Then aggregate and combine SUM() window function with MAX() aggregate function to get the column unread_count:

WITH cte AS (
  SELECT t.chat_id, t.content,
         FIRST_VALUE(t.content) OVER (PARTITION BY t.chat_id ORDER BY t.time DESC) last_content,
         (r->>'username') username
  FROM tablename t LEFT JOIN json_array_elements(read_times::json) r ON true
)
SELECT DISTINCT c.chat_id, MAX(c.last_content) "content",
       SUM((MAX((COALESCE(username, '') = 'user1')::int) = 0)::int) OVER (PARTITION BY c.chat_id) unread_count
FROM cte c
GROUP BY c.chat_id, c.content
ORDER BY c.chat_id

See the demo.

answered Jul 26 '21 at 15:42

forpas

160,666
10
38
76

Thanks, can you select other columns such as `time`? – Morteza Malvandi Jul 27 '21 at 04:36
@MortezaMalvandi check this: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f57c48fd2fbfd24daa4e81efa10618b6 – forpas Jul 27 '21 at 07:18
It's not correct, change time of row with id `3` to `t0`. – Morteza Malvandi Jul 27 '21 at 07:35
@MortezaMalvandi There is no t0 in your sample data. The query returns the max time of each chat_id. – forpas Jul 27 '21 at 07:40
Becuase of I said select all column of last selected row. I think have to use as `FIRST_VALUE(t.column_name) OVER (PARTITION BY t.chat_id ORDER BY t.time DESC) last_column_name`. – Morteza Malvandi Jul 27 '21 at 07:43
@MortezaMalvandi you can do this for any column, but for time it's simpler with MAX() OVER() because you want the max value of time. Anyway it's the same: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a47da5e2a08b7e0105a7ab5c38321c4e – forpas Jul 27 '21 at 07:52

How select a row and count row in single query in postgresql?

1 Answers1