Revision cbc1dfe0-b3de-449c-a5f7-1c089404efc9 - Stack Overflow

1. The result should be based on a table with granules 15 minutes long in the interval specified by the parameters. 
```
generate_series('2025年09月02日 14:00:00'::timestamp,'2025年09月02日 18:00:00'::timestamp,'15 minute'::interval) grn15
```
2. Expand source JSONB 
2.1 Extract "apps" array from "app_info". Use LATERAL JOIN to use app_info column from main table. 
2.2 Extract granula_start and granula_end for 5 minute intervals 
```
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
```

3. Exact granula_start is app_date+granula_start 
```
 (date||' '||(a->>'granula_start'))::timestamp granula_start
```
4. Join rows to generated_series output on granula_start 
5. Aggregate data 

See example


``` 
select grn15
 ,count(app_name) app_count
 ,sum(count_seconds::float) seconds_count
from(
select *
from generate_series('2025年09月02日 14:00:00'::timestamp,'2025年09月02日 18:00:00'::timestamp,'15 minute'::interval) grn15
left join(
select employee_id meid, date -- ,app_info
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start
 ,(date||' '||(a->>'granula_end'))::timestamp granula_end
 ,b.value->>'date' app_date
 ,b.value->>'app_name' app_name
 ,b.value->>'type_name' type_name
 ,b.value->>'domain_site' domain_site
 ,b.value->>'employee_id' app_employee_id
 ,b.value->>'count_seconds' count_seconds
 ,b.value app_data
from my_temp
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
)q1 on q1.granula_start::timestamp>=grn15 and q1.granula_end::timestamp< (grn15+'15 minute'::interval)
)q2
group by grn15
order by grn15 -- ,granula_start
```

| grn15 | app\_count | seconds\_count |
|:------|----------:|:--------------|
| 2025年09月02日 14:00:00 | 0 | *null* |
| 2025年09月02日 14:15:00 | 0 | *null* |
| 2025年09月02日 14:30:00 | 0 | *null* |
| 2025年09月02日 14:45:00 | 4 | 1371.9560000000001 |
| 2025年09月02日 15:00:00 | 0 | *null* |
| 2025年09月02日 15:15:00 | 0 | *null* |
| 2025年09月02日 15:30:00 | 0 | *null* |
| 2025年09月02日 15:45:00 | 0 | *null* |
| 2025年09月02日 16:00:00 | 0 | *null* |
| 2025年09月02日 16:15:00 | 0 | *null* |
| 2025年09月02日 16:30:00 | 0 | *null* |
| 2025年09月02日 16:45:00 | 0 | *null* |
| 2025年09月02日 17:00:00 | 0 | *null* |
| 2025年09月02日 17:15:00 | 0 | *null* |
| 2025年09月02日 17:30:00 | 2 | 111.471 |
| 2025年09月02日 17:45:00 | 2 | 13.51 |
| 2025年09月02日 18:00:00 | 0 | *null* |

Expand JSONB column to table.

``` 
select grn15, meid, date, granula_start, granula_end, count_seconds, app_date, app_name
from generate_series('2025年09月02日 14:00:00'::timestamp,'2025年09月02日 18:00:00'::timestamp,'15 minute'::interval) grn15
left join(
select employee_id meid, date -- ,app_info
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start
 ,(date||' '||(a->>'granula_end'))::timestamp granula_end
 ,b.value->>'date' app_date
 ,b.value->>'app_name' app_name
 ,b.value->>'type_name' type_name
 ,b.value->>'domain_site' domain_site
 ,b.value->>'employee_id' app_employee_id
 ,b.value->>'count_seconds' count_seconds
 ,b.value app_data
from my_temp
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
)q1 on q1.granula_start::timestamp>=grn15 and q1.granula_end::timestamp< (grn15+'15 minute'::interval)
order by grn15,granula_start
```

| grn15 | meid | date | granula\_start | granula\_end | count\_seconds | app\_date | app\_name |
|:------|:-----|:-----|:--------------|:------------|:--------------|:---------|:---------|
| 2025年09月02日 14:00:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 14:15:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 14:30:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 14:45:00 | 1 | 2025年09月02日 | 2025年09月02日 14:45:00 | 2025年09月02日 14:50:00 | 155.334 | 2025年07月31日 | Postman |
| 2025年09月02日 14:45:00 | 1 | 2025年09月02日 | 2025年09月02日 14:50:00 | 2025年09月02日 14:55:00 | 282.565 | 2025年07月31日 | CLion |
| 2025年09月02日 14:45:00 | 1 | 2025年09月02日 | 2025年09月02日 14:50:00 | 2025年09月02日 14:55:00 | 459.091 | 2025年07月31日 | WinSCP: SFTP, FTP, WebDAV, S3 and SCP client |
| 2025年09月02日 14:45:00 | 1 | 2025年09月02日 | 2025年09月02日 14:50:00 | 2025年09月02日 14:55:00 | 474.966 | 2025年07月31日 | Explorer |
| 2025年09月02日 15:00:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 15:15:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 15:30:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 15:45:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 16:00:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 16:15:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 16:30:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 16:45:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 17:00:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 17:15:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |
| 2025年09月02日 17:30:00 | 1 | 2025年09月02日 | 2025年09月02日 17:35:00 | 2025年09月02日 17:40:00 | 66.195 | 2025年07月31日 | CLion |
| 2025年09月02日 17:30:00 | 1 | 2025年09月02日 | 2025年09月02日 17:35:00 | 2025年09月02日 17:40:00 | 45.276 | 2025年07月31日 | Postman |
| 2025年09月02日 17:45:00 | 1 | 2025年09月02日 | 2025年09月02日 17:50:00 | 2025年09月02日 17:55:00 | 3.86 | 2025年07月31日 | CLion |
| 2025年09月02日 17:45:00 | 1 | 2025年09月02日 | 2025年09月02日 17:50:00 | 2025年09月02日 17:55:00 | 9.65 | 2025年07月31日 | Postman |
| 2025年09月02日 18:00:00 | *null* | *null* | *null* | *null* | *null* | *null* | *null* |

``` 
select employee_id meid, date -- ,app_info
 ,(date||' '||(a->>'granula_start'))::timestamp granula_start
 ,(date||' '||(a->>'granula_end'))::timestamp granula_end
-- ,b.value->>'date' app_date
 ,b.value->>'app_name' app_name
-- ,b.value->>'type_name' type_name
-- ,b.value->>'domain_site' domain_site
-- ,b.value->>'employee_id' app_employee_id
 ,b.value->>'count_seconds' count_seconds
 ,jsonb_pretty(b.value) app_data
from my_temp
cross join lateral jsonb_array_elements(app_info) a
cross join lateral jsonb_array_elements((a->>'apps')::jsonb) b
order by meid,date,	granula_start
```

| meid | date | granula\_start | granula\_end | app\_name | count\_seconds | app\_data |
|:-----|:-----|:--------------|:------------|:---------|:--------------|:---------|
| 1 | 2025年09月02日 | 2025年09月02日 14:45:00 | 2025年09月02日 14:50:00 | Postman | 155.334 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "Postman",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 155.334<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 14:50:00 | 2025年09月02日 14:55:00 | CLion | 282.565 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "CLion",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 282.565<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 14:50:00 | 2025年09月02日 14:55:00 | WinSCP: SFTP, FTP, WebDAV, S3 and SCP client | 459.091 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "WinSCP: SFTP, FTP, WebDAV, S3 and SCP client",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 459.091<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 14:50:00 | 2025年09月02日 14:55:00 | Explorer | 474.966 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "Explorer",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 474.966<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 16:10:00 | 2025年09月02日 16:15:00 | CLion | 243.496 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "CLion",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 243.496<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 16:10:00 | 2025年09月02日 16:15:00 | Postman | 319.902 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "Postman",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 319.902<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 16:55:00 | 2025年09月02日 17:00:00 | CLion | 192.897 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "CLion",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 192.897<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 16:55:00 | 2025年09月02日 17:00:00 | Postman | 111.307 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "Postman",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 111.307<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 16:55:00 | 2025年09月02日 17:00:00 | Explorer | 144.277 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "Explorer",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 144.277<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 17:35:00 | 2025年09月02日 17:40:00 | CLion | 66.195 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "CLion",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 66.195<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 17:35:00 | 2025年09月02日 17:40:00 | Postman | 45.276 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "Postman",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 45.276<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 17:50:00 | 2025年09月02日 17:55:00 | CLion | 3.86 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "CLion",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 3.86<br>} |
| 1 | 2025年09月02日 | 2025年09月02日 17:50:00 | 2025年09月02日 17:55:00 | Postman | 9.65 | {<br>    "date": "2025年07月31日",<br>    "app\_name": "Postman",<br>    "type\_name": "Native",<br>    "domain\_site": "",<br>    "employee\_id": "2223eb0f0d0c4941a16e83dc7274771b",<br>    "count\_seconds": 9.65<br>} |


[fiddle](https://dbfiddle.uk/ZTym21Fa) 
[and](https://dbfiddle.uk/nxR7ytsz)