EN VI

Sql - Calculate occurrences and insert into json on the fly in postgres?

How to Sql - Calculate occurrences and insert into json on the fly in postgres

I have a table like below

id |date                   |browser
---+———————————————————————+——————————————————————-
101|2024-03-12 00:00:00.000|Chrome |
102|2024-03-12 00:00:00.000|Firefox|
103|2024-03-13 00:00:00.000|Chrome |
104|2024-03-13 00:00:00.000|Firefox|
105|2024-03-13 00:00:00.000|Brave  |
106|2024-03-14 00:00:00.000|Chrome |
107|2024-03-14 00:00:00.000|Firefox|
108|2024-03-14 00:00:00.000|Edge   |

I want to count the occurances and insert the number of occurances in another database like below (into jsonb column). The browser is just as example of a string and can have many different type of values (i.e. it is not an enum type, so the values are unknown.)

id |date                   |json_count
---+———————————————————————+———————————————————————————————————————-
101|2024-03-12 00:00:00.000|{“Chrome”: 1, “Firefox”: 1}            |
102|2024-03-13 00:00:00.000|{“Chrome”: 1, “Firefox”: 1, “Brave”: 1}|
103|2024-03-14 00:00:00.000|{“Chrome”: 1, “Firefox”: 1, “Edge”: 1} |

How do i achieve this in a sql query?

Solution:

We need to count the occurances by date and browser using GROUP BY and COUNT(), then apply jsonb_object_agg to aggregate browser/count pairs as a JSON object using jsonb_object_agg() :

select min(id) as id, date, json_object_agg(browser, cnt) as json_count
from (
  select date, browser, min(id) as id, count(*) as cnt
  from mytable
  group by date, browser
)
group by date;

Results :

id  date                json_count
101 2024-03-12 00:00:00 {"Chrome": 1, "Firefox": 1}
106 2024-03-14 00:00:00 {"Edge": 1, "Chrome": 1, "Firefox": 1}
103 2024-03-13 00:00:00 {"Brave": 1, "Chrome": 1, "Firefox": 1}

Demo here