postgres jsonb array aggregate functions
Please help me write a query. I have a table with data on user applications. It contains several fields: employee_id text, date date, app_info jsonb. The info field app_info contains the following code:
[
{
"apps": [
{
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 155.334
}
],
"granula_end": "14:50:00",
"granula_start": "14:45:00"
},
{
"apps": [
{
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 282.565
},
{
"date": "2025年07月31日",
"app_name": "WinSCP: SFTP, FTP, WebDAV, S3 and SCP client",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 459.091
},
{
"date": "2025年07月31日",
"app_name": "Проводник",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 474.966
}
],
"granula_end": "14:55:00",
"granula_start": "14:50:00"
},
{
"apps": null,
"granula_end": "15:00:00",
"granula_start": "14:55:00"
},
{
"apps": null,
"granula_end": "15:05:00",
"granula_start": "15:00:00"
},
{
"apps": null,
"granula_end": "15:10:00",
"granula_start": "15:05:00"
},
{
"apps": null,
"granula_end": "15:15:00",
"granula_start": "15:10:00"
},
{
"apps": null,
"granula_end": "15:20:00",
"granula_start": "15:15:00"
},
{
"apps": null,
"granula_end": "15:25:00",
"granula_start": "15:20:00"
},
{
"apps": null,
"granula_end": "15:30:00",
"granula_start": "15:25:00"
},
{
"apps": null,
"granula_end": "15:35:00",
"granula_start": "15:30:00"
},
{
"apps": null,
"granula_end": "15:40:00",
"granula_start": "15:35:00"
},
{
"apps": null,
"granula_end": "15:45:00",
"granula_start": "15:40:00"
},
{
"apps": null,
"granula_end": "15:50:00",
"granula_start": "15:45:00"
},
{
"apps": null,
"granula_end": "15:55:00",
"granula_start": "15:50:00"
},
{
"apps": null,
"granula_end": "16:00:00",
"granula_start": "15:55:00"
},
{
"apps": null,
"granula_end": "16:05:00",
"granula_start": "16:00:00"
},
{
"apps": null,
"granula_end": "16:10:00",
"granula_start": "16:05:00"
},
{
"apps": [
{
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 243.496
},
{
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 319.902
}
],
"granula_end": "16:15:00",
"granula_start": "16:10:00"
},
{
"apps": null,
"granula_end": "16:20:00",
"granula_start": "16:15:00"
},
{
"apps": null,
"granula_end": "16:25:00",
"granula_start": "16:20:00"
},
{
"apps": null,
"granula_end": "16:30:00",
"granula_start": "16:25:00"
},
{
"apps": null,
"granula_end": "16:35:00",
"granula_start": "16:30:00"
},
{
"apps": null,
"granula_end": "16:40:00",
"granula_start": "16:35:00"
},
{
"apps": null,
"granula_end": "16:45:00",
"granula_start": "16:40:00"
},
{
"apps": null,
"granula_end": "16:50:00",
"granula_start": "16:45:00"
},
{
"apps": null,
"granula_end": "16:55:00",
"granula_start": "16:50:00"
},
{
"apps": [
{
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 192.897
},
{
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 111.307
},
{
"date": "2025年07月31日",
"app_name": "Проводник",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 144.277
}
],
"granula_end": "17:00:00",
"granula_start": "16:55:00"
},
{
"apps": null,
"granula_end": "17:05:00",
"granula_start": "17:00:00"
},
{
"apps": null,
"granula_end": "17:10:00",
"granula_start": "17:05:00"
},
{
"apps": null,
"granula_end": "17:15:00",
"granula_start": "17:10:00"
},
{
"apps": null,
"granula_end": "17:20:00",
"granula_start": "17:15:00"
},
{
"apps": null,
"granula_end": "17:25:00",
"granula_start": "17:20:00"
},
{
"apps": null,
"granula_end": "17:30:00",
"granula_start": "17:25:00"
},
{
"apps": null,
"granula_end": "17:35:00",
"granula_start": "17:30:00"
},
{
"apps": [
{
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 66.195
},
{
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 45.276
}
],
"granula_end": "17:40:00",
"granula_start": "17:35:00"
},
{
"apps": null,
"granula_end": "17:45:00",
"granula_start": "17:40:00"
},
{
"apps": null,
"granula_end": "17:50:00",
"granula_start": "17:45:00"
},
{
"apps": [
{
"date": "2025年07月31日",
"app_name": "CLion",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 3.86
},
{
"date": "2025年07月31日",
"app_name": "Postman",
"type_name": "Native",
"domain_site": "",
"employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
"count_seconds": 9.65
}
],
"granula_end": "17:55:00",
"granula_start": "17:50:00"
},
{
"apps": null,
"granula_end": "18:00:00",
"granula_start": "17:55:00"
}
]
I have the following task: For a given date range, calculate the number of minutes in each application with an interval of 15 minutes. That is, I need to combine the existing 5-minute granules into 15-minute granules and calculate the amount of time for all applications in this granule for each granule. Group the list of applications itself so that there are no duplicates. I tried to do this, but I can’t figure out how to write the query correctly. Here is a link to my work - https://dbfiddle.uk/q88VfV3l Hello. Please help me write a query. I have a table with data on user applications. It contains several fields: The info field contains the following code I have the following task: For a given date range, calculate the number of minutes in each application with an interval of 15 minutes. That is, I need to combine the existing 5-minute granules into 15-minute granules and calculate the amount of time for all applications in this granule for each granule. Group the list of applications itself so that there are no duplicates. I tried to do this, but I can’t figure out how to write the query correctly. Here is a link to my work - and I need a second query that finds the largest granule by application runtime. That is, where the greatest amount of work is in different applications. I don’t understand how to write this query at all... Please help me.
Link my experiment - https://dbfiddle.uk/q88VfV3l
P.S. I looked through different questions on the forum, but none of them helped me solve the problem, so I posted my question.
- 65
- 3