Question Details

No question body available.

Tags

arrays postgresql aggregate-functions jsonb

Answers (2)

Accepted Answer Available
Accepted Answer
September 2, 2025 Score: 1 Rep: 8,714 Quality: High Completeness: 100%
  1. The result should be based on a table with granules 15 minutes long in the interval specified by the parameters.
generateseries('2025-09-02 14:00:00'::timestamp,'2025-09-02 18:00:00'::timestamp,'15 minute'::interval) grn15
  1. Expand source JSONB
    2.1 Extract "apps" array from "app    info". Use LATERAL JOIN to use appinfo column from main table. 2.2 Extract granulastart and granulaend for 5 minute intervals
cross join lateral jsonbarrayelements(appinfo) a
cross join lateral jsonbarrayelements((a->>'apps')::jsonb) b
  1. Exact granulastart is appdate+granulastart
  (date||' '||(a->>'granulastart'))::timestamp granulastart
  1. Join rows to generatedseries output on granulastart
  2. Aggregate data

See example

select grn15
  ,count(appname) appcount
  ,sum(countseconds::float) secondscount
from(
select *
from generateseries('2025-09-02 14:00:00'::timestamp,'2025-09-02 18:00:00'::timestamp,'15 minute'::interval) grn15
left join(
select employeeid meid, date -- ,appinfo
  ,(date||' '||(a->>'granulastart'))::timestamp granulastart
  ,(date||' '||(a->>'granulaend'))::timestamp granulaend
  ,b.value->>'date' appdate
  ,b.value->>'appname' appname
  ,b.value->>'typename' typename
  ,b.value->>'domainsite' domainsite
  ,b.value->>'employeeid' appemployeeid
  ,b.value->>'countseconds' countseconds
  ,b.value appdata
from mytemp
cross join lateral jsonbarrayelements(appinfo) a
cross join lateral jsonbarrayelements((a->>'apps')::jsonb) b
)q1 on q1.granulastart::timestamp>=grn15 and q1.granulaend::timestamp< (grn15+'15 minute'::interval)
)q2
group by grn15
order by grn15 -- ,granulastart
grn15 appcount secondscount
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, granulastart, granulaend, countseconds, appdate, appname
from generateseries('2025-09-02 14:00:00'::timestamp,'2025-09-02 18:00:00'::timestamp,'15 minute'::interval) grn15
left join(
select employeeid meid, date -- ,appinfo
  ,(date||' '||(a->>'granulastart'))::timestamp granulastart
  ,(date||' '||(a->>'granulaend'))::timestamp granulaend
  ,b.value->>'date' appdate
  ,b.value->>'appname' appname
  ,b.value->>'typename' typename
  ,b.value->>'domainsite' domainsite
  ,b.value->>'employeeid' appemployeeid
  ,b.value->>'countseconds' countseconds
  ,b.value appdata
from mytemp
cross join lateral jsonbarrayelements(appinfo) a
cross join lateral jsonbarrayelements((a->>'apps')::jsonb) b
)q1 on q1.granulastart::timestamp>=grn15 and q1.granulaend::timestamp< (grn15+'15 minute'::interval)
order by grn15,granulastart
grn15 meid date granulastart granulaend countseconds appdate appname
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 employeeid meid, date -- ,appinfo
  ,(date||' '||(a->>'granulastart'))::timestamp granulastart
  ,(date||' '||(a->>'granulaend'))::timestamp granulaend
--  ,b.value->>'date' appdate
  ,b.value->>'appname' appname
--  ,b.value->>'typename' typename
--  ,b.value->>'domainsite' domainsite
--  ,b.value->>'employeeid' appemployeeid
  ,b.value->>'countseconds' countseconds
  ,jsonbpretty(b.value) appdata
from mytemp
cross join lateral jsonbarrayelements(appinfo) a
cross join lateral jsonbarrayelements((a->>'apps')::jsonb) b
order by meid,date, granulastart
meid date granulastart granulaend appname countseconds appdata
1 2025-09-02 2025-09-02 14:45:00 2025-09-02 14:50:00 Postman 155.334 {
    "date": "2025-07-31",
    "appname": "Postman",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 155.334
}
1 2025-09-02 2025-09-02 14:50:00 2025-09-02 14:55:00 CLion 282.565 {
    "date": "2025-07-31",
    "appname": "CLion",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 282.565
}
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 {
    "date": "2025-07-31",
    "appname": "WinSCP: SFTP, FTP, WebDAV, S3 and SCP client",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 459.091
}
1 2025-09-02 2025-09-02 14:50:00 2025-09-02 14:55:00 Explorer 474.966 {
    "date": "2025-07-31",
    "appname": "Explorer",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 474.966
}
1 2025-09-02 2025-09-02 16:10:00 2025-09-02 16:15:00 CLion 243.496 {
    "date": "2025-07-31",
    "appname": "CLion",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 243.496
}
1 2025-09-02 2025-09-02 16:10:00 2025-09-02 16:15:00 Postman 319.902 {
    "date": "2025-07-31",
    "appname": "Postman",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 319.902
}
1 2025-09-02 2025-09-02 16:55:00 2025-09-02 17:00:00 CLion 192.897 {
    "date": "2025-07-31",
    "appname": "CLion",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 192.897
}
1 2025-09-02 2025-09-02 16:55:00 2025-09-02 17:00:00 Postman 111.307 {
    "date": "2025-07-31",
    "appname": "Postman",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 111.307
}
1 2025-09-02 2025-09-02 16:55:00 2025-09-02 17:00:00 Explorer 144.277 {
    "date": "2025-07-31",
    "appname": "Explorer",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 144.277
}
1 2025-09-02 2025-09-02 17:35:00 2025-09-02 17:40:00 CLion 66.195 {
    "date": "2025-07-31",
    "appname": "CLion",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 66.195
}
1 2025-09-02 2025-09-02 17:35:00 2025-09-02 17:40:00 Postman 45.276 {
    "date": "2025-07-31",
    "appname": "Postman",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 45.276
}
1 2025-09-02 2025-09-02 17:50:00 2025-09-02 17:55:00 CLion 3.86 {
    "date": "2025-07-31",
    "appname": "CLion",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 3.86
}
1 2025-09-02 2025-09-02 17:50:00 2025-09-02 17:55:00 Postman 9.65 {
    "date": "2025-07-31",
    "appname": "Postman",
    "typename": "Native",
    "domainsite": "",
    "employeeid": "2223eb0f0d0c4941a16e83dc7274771b",
    "countseconds": 9.65
}

fiddle
and

September 2, 2025 Score: 1 Rep: 7,653 Quality: Low Completeness: 100%

I understand that you want to:

  1. unpack the 5 minutes jsonb created by this previous question,
  2. regroup them by 15 minutes slices (cumulating countseconds for applications that appear more than once over the three 5 minutes granules of the 15 minute "big granule"), and
  3. then determine the most active quarter of an hour (for a given user and day?).

As all facilities for 2. exist in SQL (group by, sum, datebin), we will handle 1. by first unwrapping our jsonb columns to full SQL records (as part of a CTE) with all columns present for grouping and suming.
Finally 3. will simply be done with the rownumber() window function, to get each granule's position relatively to other granules of the same day and user, ordered by decreasing countseconds (thus all granules with rownumber() 1 are the most active of their user and day, having the biggest countseconds).

with
  -- Start by renormalizing each jsonb back to a "granule" CTE:
  granule as
  (
    select
      employeeid, date,
      datebin('15 minutes', date + (granule->>'granulastart')::time, date) biggranula, -- Here prepare our grouping column: granulastart truncated to the preceding 15 mn alignment.
      granule->'granulastart', granule->'granulaend',
      app->'appname' appname, app->'typename' typename, app->'domainsite' domainsite,
      (app->'countseconds')::decimal countseconds
    from mytemp t
    cross join lateral jsonbarrayelements(appinfo) granule -- pop each t.appinfo (a jsonb array) into its own row
    cross join lateral jsonbarrayelements(granule->'apps') app -- pop each granule->'apps' (itself a jsonb array) into its own row
    where jsonbtypeof(granule->'apps') = 'array' -- /!\ This filter avoids the just above jsonbarrayelements(granule->'apps') to crash on nulls with "cannot extract elements from a scalar". However it looks fragile, because the optimizer could choose to jsonbarrayelements() before this jsontypeof() filter.
  ),
  -- Now, inside each granule, group by app ("Group the list of applications itself so that there are no duplicates"):
  biggranule as
  (
    select
      employeeid, date, biggranula, appname, typename, domainsite,
      sum(countseconds) countseconds
    from granule
    group by 1,2,3,4,5,6
  ),
--select * from biggranule;
  -- Now we can wrap back our grouped biggranules to JSONB:
  biggranules as
  (
    select
      employeeid, date, biggranula,
      sum(countseconds) countseconds,
      jsonagg((select r from (select appname, typename, domainsite, countseconds) r) order by countseconds desc)
    from biggranule
    group by 1, 2, 3
  )
-- Display;
-- and add a mostusedrank to answer to "a second query that finds the largest granule by application runtime":
select
  *,
  rownumber() over (partition by employeeid, date order by countseconds desc) mostusedrank
from biggranules
order by employeeid, date, biggranula;
employeeid date biggranula countseconds jsonagg mostusedrank
1 2025-08-01 2025-08-01 20:15:00 75.416000 [{"appname":"Windows Explorer","typename":"Native","domainsite":"","countseconds":57.337000},
 {"appname":"Microsoft OneDrive","typename":"Native","domainsite":"","countseconds":18.079000}]		 1
2223eb0f0d0c4941a16e83dc7274771b 2025-07-31 2025-07-31 14:45:00 1371.956000 [{"appname":"Проводник","typename":"Native","domainsite":"","countseconds":474.966000},
 {"appname":"WinSCP: SFTP, FTP, WebDAV, S3 and SCP client","typename":"Native","domainsite":"","countseconds":459.091000},
 {"appname":"CLion","typename":"Native","domainsite":"","countseconds":282.565000},
 {"appname":"Postman","typename":"Native","domainsite":"","countseconds":155.334000}]		 1
2223eb0f0d0c4941a16e83dc7274771b 2025-07-31 2025-07-31 16:00:00 563.398000 [{"appname":"Postman","typename":"Native","domainsite":"","countseconds":319.902000},
 {"appname":"CLion","typename":"Native","domainsite":"","countseconds":243.496000}]		 2
2223eb0f0d0c4941a16e83dc7274771b 2025-07-31 2025-07-31 16:45:00 448.481000 [{"appname":"CLion","typename":"Native","domainsite":"","countseconds":192.897000},
 {"appname":"Проводник","typename":"Native","domainsite":"","countseconds":144.277000},
 {"appname":"Postman","typename":"Native","domainsite":"","countseconds":111.307000}]		 3
2223eb0f0d0c4941a16e83dc7274771b 2025-07-31 2025-07-31 17:30:00 111.471000 [{"appname":"CLion","typename":"Native","domainsite":"","countseconds":66.195000},
 {"appname":"Postman","typename":"Native","domainsite":"","countseconds":45.276000}]		 4
2223eb0f0d0c4941a16e83dc7274771b 2025-07-31 2025-07-31 17:45:00 13.510000 [{"appname":"Postman","typename":"Native","domainsite":"","countseconds":9.650000},
 {"appname":"CLion","typename":"Native","domainsite":"","countseconds":3.860000}]		 5
2223eb0f0d0c4941a16e83dc7274771b 2025-08-01 2025-08-01 14:00:00 228.741000 [{"appname":"Postman","typename":"Native","domainsite":"","countseconds":216.263000},
 {"appname":"CLion","typename":"Native","domainsite":"","countseconds":12.478000}]		 1
2223eb0f0d0c4941a16e83dc7274771b 2025-08-06 2025-08-06 12:15:00 234.203000 [{"appname":"Postman","typename":"Native","domainsite":"","countseconds":135.358000},
 {"appname":"DataGrip","typename":"Native","domainsite":"","countseconds":98.845000}]		 1
2223eb0f0d0c4941a16e83dc7274771b 2025-08-07 2025-08-07 10:30:00 430.818000 [{"appname":"CLion","typename":"Native","domainsite":"","countseconds":223.109000},
 {"appname":"Postman","typename":"Native","domainsite":"","countseconds":205.182000},
 {"appname":"File Picker UI Host","typename":"Native","domainsite":"","countseconds":2.527000}]		 1
2223eb0f0d0c4941a16e83dc7274771b 2025-08-07 2025-08-07 10:45:00 259.270000 [{"appname":"Postman","typename":"Native","domainsite":"","countseconds":120.363000},
 {"appname":"CLion","typename":"Native","domainsite":"","countseconds":63.953000},
 {"appname":"DataGrip","typename":"Native","domainsite":"","countseconds":53.974000},
 {"appname":"Brave Browser","typename":"Native","domainsite":"","countseconds":18.028000},
 {"appname":"Проводник","typename":"Native","domainsite":"","countseconds":2.952000}]		 2
2223eb0f0d0c4941a16e83dc7274771b 2025-08-07 2025-08-07 11:00:00 75.119000 [{"appname":"Postman","typename":"Native","domainsite":"","countseconds":41.887000},
 {"appname":"CLion","typename":"Native","domainsite":"","countseconds":33.232000}]		 5
2223eb0f0d0c4941a16e83dc7274771b 2025-08-07 2025-08-07 11:15:00 204.564000 [{"appname":"Time Doctor 2","typename":"Native","domainsite":"","countseconds":123.377000},
 {"appname":"Windows Explorer","typename":"Native","domainsite":"","countseconds":81.187000}]		 3
2223eb0f0d0c4941a16e83dc7274771b 2025-08-07 2025-08-07 11:30:00 90.203000 [{"appname":"Windows Explorer","typename":"Native","domainsite":"","count_seconds":90.203000}] 4

(as seen running in this dbfiddle)