Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Users ("User" VARCHAR, UserDate DATE);
INSERT INTO Users VALUES
('Петя',   '2024-01-01'),
('Петя',   '2024-01-01'),
('Петя',   '2026-01-01'),
('Ваня',   '2025-01-02'),
('Ваня',   '2025-01-02'),
('Игорь',  '2025-06-10');
SELECT * FROM Users;

CREATE TABLE



INSERT 0 6




User
userdate


Петя
2024-01-01

Петя
2024-01-01

Петя
2026-01-01

Ваня
2025-01-02

Ваня
2025-01-02

Игорь
2025-06-10



SELECT 6


WITH 
cte1 (UserName, UserDate, UserAmount) AS (
  SELECT "User", UserDate, COUNT(UserDate) 
  FROM Users
  GROUP BY 1, 2
  )
SELECT * FROM cte1;


username
userdate
useramount


Игорь
2025-06-10
1

Петя
2026-01-01
1

Ваня
2025-01-02
2

Петя
2024-01-01
2



SELECT 4


WITH 
cte1 (UserName, UserDate, UserAmount) AS (
  SELECT "User", UserDate, COUNT(UserDate) 
  FROM Users
  GROUP BY 1, 2
  ),
cte2 (UserName, UserJSON) AS (
  SELECT UserName, json_object_agg(UserDate, UserAmount)
  FROM cte1
  GROUP BY 1
  )
SELECT * FROM cte2;


username
userjson


Ваня
{ "2025-01-02" : 2 }

Игорь
{ "2025-06-10" : 1 }

Петя
{ "2026-01-01" : 1, "2024-01-01" : 2 }



SELECT 3


WITH 
cte1 (UserName, UserDate, UserAmount) AS (
  SELECT "User", UserDate, COUNT(UserDate) 
  FROM Users
  GROUP BY 1, 2
  ),
cte2 (UserName, UserJSON) AS (
  SELECT UserName, json_object_agg(UserDate, UserAmount)
  FROM cte1
  GROUP BY 1
  )
SELECT json_object_agg(UserName, UserJSON) AS FinalJSON
FROM cte2;


finaljson


{ "Ваня" : { "2025-01-02" : 2 }, "Игорь" : { "2025-06-10" : 1 }, "Петя" : { "2026-01-01" : 1, "2024-01-01" : 2 } }



SELECT 1

User	userdate
Петя	2024-01-01
Петя	2024-01-01
Петя	2026-01-01
Ваня	2025-01-02
Ваня	2025-01-02
Игорь	2025-06-10

username	userjson
Ваня	{ "2025-01-02" : 2 }
Игорь	{ "2025-06-10" : 1 }
Петя	{ "2026-01-01" : 1, "2024-01-01" : 2 }