How to write SQL query by calls?

Question

PostgreSQL

Good afternoon! There is one table
in Postgresql - a list of calls from the PBX taken from the group entry with the minimum date (callstart). In other words, only records with a red label should be displayed. If you tell me how to do it, then my problem will be solved. But it would be even better if they hinted at how an additional field (for example, sips) could be implemented, in which all group records with the disposition and sip fields would be accumulated in the form of a JSON array, that is, the sips field would be added for the first record with content:

[{"disposition": "call failed", "sip": "100"}, 
{"disposition": "no answer", "sip": "101"}, 
{"disposition": "call failed", "sip": "102"}]

I would be grateful for any advice and tips.

Answer 1

Well something like:

select distinct pbx_call_id,
       first_value(clid) over (partition by pbx_call_id order by callstart) clid,
       ... -- остальные поля
  from calls;

On the second question, this is how you can collect what you need in a column

select pbx_call_id,
       jsonb_agg(json_build_object('disposition', disposition, 'sip', sip)) as sips
  from calls
 group by pbx_call_id

Answer 2

Either I didn’t understand the question, or the elementary WHERE callstart < *** is needed here, and it’s not at all clear why a separate json field should be made if it duplicates data from the table. It is not necessary to convert to json on the database side, at least in your case when the data is duplicated and not aggregated.

Answer 3

This suggests a window function:
group by pbx_call_id
sort by callstart
and take only the first ones (row_number()=1)
I'm not quite sure what row_number() will work in where, but the canvas is something like this:

SELECT 
id OVER(PARTITION BY pbx_call_id ORDER BY callstart ASC) 
FROM table 
Where ROW_NUMBER()=1

that is, we get the id (do I understand correctly that this is pk?) Of the first records for each call, well, then them or join the table itself to them